[PostgreSQL] - explain SQL分析介绍
一、图形化在线分析工具
二、执行分析语句
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
select * from ...
生成分析JSON之后，填入图形化分析页面，进行分析。
三、分析样例
1、走索引 - Index Scan Node
表示先走二级索引，再走一级索引找到数据
finds relevant records based on an Index. Index Scans perform 2 read operations: one to read the index and another to read the actual value from the table.
2、顺序扫描 - Seq Scan Node
finds relevant records by sequentially scanning the input record set. When reading from a table, Seq Scans (unlike Index Scans) perform a single read operation (only the table is read).
3、缓存 - shared
- Hit - 命中
- Read - 读磁盘
- Dirtied - 脏页(脏页包含在Hit中)
4、循环匹配 - Nested Loop
join数据，将两个结果集进行拼接
merges two record sets by looping through every record in the first set and trying to find a match in the second set. All matching records are returned.
遍历模式
索引模式
5、聚合 - Aggregate
group by 操作
groups records together based on a GROUP BY or aggregate function (like sum()).
6、排序 - Sort
order by 操作
sorts a record set based on the specified sort key.
7、数量限制 - limit
returns a specified number of rows from a record set.
8、with as 临时表 - Common table expressions - CTE
performs a sequential scan of Common Table Expression (CTE) query results. Note that results of a CTE are materialized (calculated and temporarily stored).
对公共表表达式（CTE）查询结果执行顺序扫描。注意，CTE的结果是具体化的（计算并临时存储）。
9、其他操作
- WindowAgg
- Subquery Scan
10、其他join模式图解
-
Hash Join
-
Merge Join
