postgresql の index メモです。
index を張ったときに、その index が有効かを確認するために、プランを確認することがあります。
どのようなログが出たらどうすればよいのか、ChatGPTに聞いてみました。
ChatGPTのアカウントがない方の参考にどうぞ。
◇ 質問
postgresql の index の実行計画をexplain analyze で見る場合に、遅い場合や削除したほうが良いindexの場合のログを教えてください。
◇ 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"が高い場合は、インデックスが遅いことを示しています。
この場合、インデックスを再作成するか、適切なインデックスを作成することで、クエリの実行を高速化することができます。
●削除したほうが良いindexの場合のログ例
QUERY PLAN
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
"Index Scan"に対して、"cost"や"actual time"が低い場合は、インデックスが効果的であることを示しています。
クエリの実行計画に複数のインデックスが使用される場合のログ例
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でインデックスが存在しない場合のクエリ実行のログ例は、以下のようになります。
QUERY PLAN
Seq Scan on table_name (cost=0.00..20.00 rows=1 width=34) (actual time=0.050..0.055 rows=1 loops=1)
Filter: ((col1 = 1) AND (col2 = 'example'::text))
Rows Removed by Filter: 9999
Planning time: 0.052 ms
Execution time: 0.068 ms
"Seq Scan"によって、テーブルの全ての行をスキャンしていることがわかります。
"cost"は0.00から20.00の範囲になっており、インデックスがない場合はテーブルの全ての行をスキャンするため、コストが高くなります。
インデックスが存在しない場合、パフォーマンスが低下する可能性があるため、インデックスを適切に設定することが重要です。