PostgreSQL 索引解析笔记

2 年 ago
新, 韵
2 minutes

以下是关于 PostgreSQL 索引的备忘录。
在创建索引后，有时需要检查索引是否有效，可以查看执行计划（Plan）来确认。
我向 ChatGPT 询问了如果出现什么样的日志该如何处理，供没有 ChatGPT 账户的人参考。

◇ 问题
请告诉我如何通过使用”explain analyze”来查看postgresql索引的执行计划，以及当执行计划较慢或需要删除某个索引时，如何记录相关日志。

◇ ChatGPT的回答
如果要查看PostgreSQL的索引执行计划，可以使用”EXPLAIN ANALYZE”命令。以下是索引速度较慢或需要删除索引的日志示例。
●索引速度较慢的日志示例

QUERY PLAN

Index Only Scan using idx_name on table_name (cost=0.43..8.45 rows=1 width=34) (actual time=0.014..0.015 rows=1 loops=1)
Index Cond: (name = 'example'::text)
Heap Fetches: 1
Planning time: 0.106 ms
Execution time: 0.031 ms

如果”Index Only Scan”的”cost”或者”actual time”较高，这表明索引较慢。
在这种情况下，可以通过重新创建索引或创建适当的索引来加速查询的执行。

当需要删除索引时，以下是示例日志：查询计划。

Seq Scan on table_name (cost=0.00..5558.80 rows=50000 width=10) (actual time=40.782..215.912 rows=50000 loops=1)
Filter: (col1 = 1)
Rows Removed by Filter: 500000
Planning time: 0.086 ms
Execution time: 326.343 ms

如果“Seq Scan”操作的“cost”较低且存在“Filter”，则说明索引不起作用。
在这种情况下，可以通过删除索引来加速查询执行。但是，建议在需要的情况下创建索引的备份。

◇ 我还想了解其他的解释和分析日志，请给我其他的 explain analyze 日志。

如果要检查 PostgreSQL 中索引的执行计划，可以使用 “EXPLAIN ANALYZE” 命令。以下是其他日志示例。

インデックスを使用したクエリのログ例
QUERY PLAN

Index Scan using idx_col1_col2 on table_name (cost=0.43..8.45 rows=1 width=34) (actual time=0.014..0.015 rows=1 loops=1)
Index Cond: ((col1 = 1) AND (col2 = 'example'::text))
Planning time: 0.106 ms
Execution time: 0.031 ms

如果对于”索引扫描”，”成本”和”实际时间”较低，则表示索引是有效的。

クエリの実行計画に複数のインデックスが使用される場合のログ例
QUERY PLAN

Hash Join (cost=20.17..57.11 rows=2 width=12) (actual time=1.059..1.069 rows=1 loops=1)
Hash Cond: (table1.col1 = table2.col1)
-> Bitmap Heap Scan on table1 (cost=10.03..30.25 rows=2 width=8) (actual time=0.518..0.524 rows=1 loops=1)
Recheck Cond: (col2 = 'example'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_table1_col2 (cost=0.00..10.03 rows=2 width=0) (actual time=0.332..0.332 rows=1 loops=1)
Index Cond: (col2 = 'example'::text)
-> Hash (cost=10.10..10.10 rows=6 width=8) (actual time=0.521..0.521 rows=6 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Index Scan using idx_table2_col1 on table2 (cost=0.28..10.10 rows=6 width=8) (actual time=0.048..0.505 rows=6 loops=1)
Planning time: 0.132 ms
Execution time: 1.105 ms

如果使用了多个索引，则会出现多个”Index Scan”和”Bitmap Index Scan”。
如果”Hash Join”的成本很高，可以通过改善索引来加快查询的执行速度。

如果在PostgreSQL中没有索引，查询执行的日志示例如下。
查询计划

在表名上進行順序掃描（成本=0.00..20.00，行數=1，寬度=34）（實際時間=0.050..0.055，行數=1，循環次數=1）
過濾條件：（col1 = 1）且（col2 = ‘example’::text）
被過濾器移除的行數：9999
計劃時間：0.052毫秒
執行時間：0.068毫秒

通过”Seq Scan”操作，我们可以发现它在扫描表的所有行。
“cost”的范围从0.00到20.00不等，如果没有索引，则需要扫描表的所有行，导致成本较高。
如果缺乏索引，可能会影响性能，因此正确设置索引非常重要。

bannerAds