postgreSQLでexplain analyzeの出力結果を分析した

 昨年11月頃からUbuntu-22.04.5からLinuxMint-21.3へ移行をしました。まだ、全ての移行を確認できていませんが、今年からはLinuxMintを主体に使う予定です。ubuntultsがBaseのディストリビューションなのでUbuntuとの大差はありません。Ubuntu-20.04までに近い環境へ移行できたと思っています。

1.概要

昔勉強をしたことでも時間が経過すると忘れてしまうことは沢山あります。RDBでSQLが実行されるときに統計情報が参照されて具体的な検索手順が決まることは多数経験をしていました。同じような性能問題はどこでも発生するものですが、原因究明は基本を正しく把握しているか否かの影響が大きいと思います。posrgreSQLのexplain analyzeに関して調べたのでその内容を記述します。

3.詳細

データ項目は下記５項目で、データ件数は200万件です。
データの例
date             daytime                     number  real  real   
2025-12-27,2025-12-27 16:39:16,1,0.924046,0.593909

実行環境はi3-7100です。
primary　keyはdaytime、2次key(index)はnumberとします。
insertで200万件のデータを登録後、下記select文を調べてみました。
select * from my_table where number = '1000000'
毎回drop tableを実施後、insertからやり直しをしました。

(1)primary keyのみ

 Gather  (cost=1000.00..26106.97 rows=7353 width=64) (actual time=108.051..111.861 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on my_table  (cost=0.00..24371.67 rows=3064 width=64) (actual time=88.469..105.677 rows=0 loops=3)
         Filter: (number = 1000000)
         Rows Removed by Filter: 666666
 Planning Time: 0.273 ms
 Execution Time: 111.895 ms

(2)primary key, 2次keyあり

 Bitmap Heap Scan on my_table  (cost=169.41..13512.40 rows=7353 width=64) (actual time=0.069..0.069 rows=1 loops=1)
   Recheck Cond: (number = 1000000)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_number  (cost=0.00..167.58 rows=7353 width=0) (actual time=0.032..0.032 rows=1 loops=1)
         Index Cond: (number = 1000000)
 Planning Time: 0.275 ms
 Execution Time: 0.098 ms

(3)primary key, 2次keyあり,vacuumdb実行

 Index Scan using idx_number on my_table  (cost=0.43..8.45 rows=1 width=31) (actual time=0.038..0.039 rows=1 loops=1)
   Index Cond: (number = 1000000)
 Planning Time: 0.319 ms
 Execution Time: 0.072 ms

3.所見

Parallet Seq Scan => Bitmap Index Scan => Index Scanと変化しており、正しいindexを作成して、統計情報を正しくすることで最適な検索処理がされています。正しい知識で正しい設計を行い、更に正しい手順で処理をして、予測される結果が出ることを正しく理解することが大切だと再認識しました。