django-query-plan-reading

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Django Query Plan Reading

Django 查询计划解读

Use this skill when the expensive unit is a specific SQL statement or queryset. The goal is to explain why the database is doing work, not to guess from the ORM code.

当性能瓶颈是特定SQL语句或queryset时，可使用本技能。目标是解释数据库执行操作的原因，而非通过ORM代码猜测。

Workflow

工作流程

Get the exact query.
- Prefer the queryset that produced it.
- If starting from logged SQL, include bound parameters or representative literals.
Generate a plan.
- Use
```
queryset.explain()
```
  for ORM-owned SQL.
- Use database
```
EXPLAIN
```
  for raw SQL, views, materialized views, or SQL copied from logs.
- Use
```
analyze=True
```
  only in a safe environment because the database executes the query.
Read from the deepest node outward.
- Identify table scans, index scans, joins, sorts, aggregations, and limits.
- Compare estimated rows with actual rows when using analyze.
- Look for high-cost nodes that feed many rows to later nodes.
Decide the next change.
- Missing selective access path: use
```
django-index-design
```
  .
- Query shape prevents useful index access: rewrite filters, ordering, or join strategy.
- Large unavoidable aggregation: consider
```
django-db-side-computation
```
  or
```
django-materialized-views
```
  .
- Deep offset cost: use
```
django-pagination-performance
```
  .
Re-run the same plan after the change.
- Compare scan type, row counts, sort nodes, heap fetches, buffers, planning time, and execution time.

See explain-checklist.md for plan-reading cues and before/after review notes.

获取精确的查询语句。
- 优先选择生成该查询的queryset。
- 如果从已记录的SQL入手，请包含绑定参数或具有代表性的字面量。
生成执行计划。
- 对于ORM生成的SQL，使用
```
queryset.explain()
```
  。
- 对于原生SQL、视图、物化视图或从日志中复制的SQL，使用数据库的
```
EXPLAIN
```
  命令。
- 仅在安全环境中使用
```
analyze=True
```
  ，因为数据库会实际执行该查询。
从最内层节点向外解读。
- 识别表扫描、索引扫描、连接、排序、聚合和限制操作。
- 使用analyze时，对比估算行数与实际行数。
- 留意那些会向下游节点传递大量数据的高成本节点。
确定下一步调整方案。
- 缺少选择性访问路径：使用
```
django-index-design
```
  技能。
- 查询结构无法利用有效索引访问：重写过滤条件、排序方式或连接策略。
- 存在无法避免的大规模聚合：考虑使用
```
django-db-side-computation
```
  或
```
django-materialized-views
```
  技能。
- 深层偏移成本过高：使用
```
django-pagination-performance
```
  技能。
修改后重新生成相同的执行计划。
- 对比扫描类型、行数、排序节点、堆读取次数、缓冲区、规划时间和执行时间。

查看explain-checklist.md获取计划解读提示以及修改前后的对比要点。

Safety Notes

安全注意事项

```
EXPLAIN ANALYZE
```
executes the query. Avoid it for mutations, unsafe functions, and production paths unless you know the impact.
A sequential scan is not automatically bad. It can be best when the table is small or the predicate returns much of the table.
A used index is not automatically good. Random heap access, bad cardinality estimates, or a post-index sort can still dominate.

```
EXPLAIN ANALYZE
```
会实际执行查询。除非清楚其影响，否则避免在数据变更操作、不安全函数和生产路径中使用。
顺序扫描并非一定是坏的。当表很小或查询条件返回表中大部分数据时，顺序扫描可能是最优选择。
使用索引也并非一定是好的。随机堆访问、错误的基数估算或索引后的排序操作仍可能成为性能瓶颈。

Verification

验证阶段

Finish with the before/after plan excerpt and a plain explanation of which node changed and why that matters.

最后附上修改前后的执行计划片段，并简要说明哪个节点发生了变化以及该变化的重要性。