PostgreSQL) 比較演算子で実行計画全然違う件

TL;DR

  • 簡単な検証で、比較演算子(<, <=, between, > and <, >= and <=)でインデックス使ったり使わなかったりする
    • 件数によらず、この演算子では絶対使われる or 絶対使われない だった
    • ただし特に検証初期で、別のテーブルスキャンノードになる類似クエリを流したら、それまでシーケンシャルスキャンなのにそれ以降インデックススキャンに切り替わる(その逆もあり)ことがありました(検証途中からは安定した感があります。結果一覧に乗せてるものは結果が安定した状態のものです)。
  • 検証環境は無風状態で本番稼働とは稼働状況が全然違うのと、実行計画に影響を及ぼす要素(パラメータ, 統計情報(テーブルのサイズやデッドタプル, カーディナリティ, スペック, etc.), キャッシュ状況, データ量, 選択率, etc.)で全然違う結果になると思うのであくまで参考まで

結果一覧

  • 「検証クエリのwhere句」列について、100万件選択でシーケンシャルスキャンになる場合は、徐々に件数を減らした場合にインデックススキャンになるかも試したので(最終的に1件選択)、where句が2通りあります
    • 結果、シーケンシャルスキャンになるものは件数変えてもシーケンシャルスキャンでした

比較演算子テーブルスキャンノード検証クエリのwhere句説明
<インデックススキャンc1 < 1000001100万件取得なのにインデックススキャン
<=シーケンシャルスキャン・c1 <= 1000000
・c1 <= 1		1件取得でもシーケンシャルスキャン
betweenインデックススキャンc1 between 1 and 1000000100万件取得なのにインデックススキャン
> and <シーケンシャルスキャン・c1 > 0 and c1 < 1000001
・c1 > 0 and c1 < 2		1件取得でもシーケンシャルスキャン
>= and <=インデックススキャンc1 >= 1 and c1 <= 1000000100万件取得なのにインデックススキャン

検証方法

  • t1テーブルに100万件投入
    • c1/c2カラム(どちらもinteger)にそれぞれ1～100万までの数値を順番に投入
  • c1カラムにBツリーインデックス作成
  • 統計情報取得
    • ちなみに未取得でも結果は同じだった
  • 各比較演算子で全件取得する条件をセットしてクエリを実行
    • explain analyze select c2 from t1 where～
    • whereまでは各クエリで同じ。where以降を変えてる(以下)
-- <
c1 < 1000001;

-- <=
c1 <= 1000000;

-- between
c1 between 1 and 1000000;

-- > and <
c1 > 0 and c1 < 1000001;

-- >= and <=
c1 >= 1 and c1 <= 1000000;
  • <=と >and < のみシーケンシャルスキャンになったので、件数を100万件から10万件 > 1万件 > 1000件 > 100件 > 10件 > 1件と縮小して実施
  • 件数変えても実行計画変わらなかった (選択率10%切るくらいからインデックススキャンになるかと思ったけど予想と違った)

検証環境情報

  • クライアント: Ubuntu22.04 + psql14.8
  • サーバ: Aurora PostgreSQL 15.3
    • db.r6i.large
    • パラメータはほぼデフォルト(time_zoneのみ変更済み)

その他

  • アプリケーションから発行する場合はワイルドカード指定は使わないと思うけど、psqlなのでワイルドカード使うとあまりインデックス使われない
  • 特に検証初期の方で、実行計画が安定しない感じがあった。シーケンシャルスキャンだったクエリが、インデックススキャンが使われる比較演算子のクエリ流したあとに、インデックススキャンになったり。
    • そうなっちゃうともう元の状態を再現できないので、この記事に乗せたのは検証後半で実行計画が安定してきた感がある状態
    • こういうの(実行計画の変動)が本番で問題になるんだよなあ

検証ログ

pfs=> explain analyze select c2 from t1 where c1 < 1000001;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using i1 on t1  (cost=0.42..32908.43 rows=1000000 width=4) (actual time=0.015..222.267 rows=1000000 loops=1)
   Index Cond: (c1 < 1000001)
 Planning Time: 0.277 ms
 Execution Time: 256.129 ms
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: 1989683979, Plan Hash: 26892416, Minimum Cost Plan Hash: 504217123
(6 rows)

Time: 266.535 ms
pfs=>
pfs=> explain analyze select c2 from t1 where c1 <= 1000000;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..16925.00 rows=1000000 width=4) (actual time=0.005..77.221 rows=1000000 loops=1)
   Filter: (c1 <= 1000000)
 Planning Time: 0.092 ms
 Execution Time: 99.773 ms
(4 rows)

Time: 109.741 ms
pfs=>
pfs=> explain analyze select c2 from t1 where c1 <= 1;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..16925.00 rows=1 width=4) (actual time=0.005..37.136 rows=1 loops=1)
   Filter: (c1 <= 1)
   Rows Removed by Filter: 999999
 Planning Time: 0.287 ms
 Execution Time: 37.147 ms
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: 1354171614, Plan Hash: 504217123, Minimum Cost Plan Hash: 26892416
(7 rows)

Time: 46.762 ms
pfs=>
pfs=> explain analyze select c2 from t1 where c1 between 1 and 1000000;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using i1 on t1  (cost=0.42..35408.43 rows=1000000 width=4) (actual time=0.029..181.235 rows=1000000 loops=1)
   Index Cond: ((c1 >= 1) AND (c1 <= 1000000))
 Planning Time: 0.419 ms
 Execution Time: 209.186 ms
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: -825175605, Plan Hash: 26892416, Minimum Cost Plan Hash: 504217123
(6 rows)

Time: 220.033 ms
pfs=>
pfs=> explain analyze select c2 from t1 where c1 > 0 and c1 < 1000001;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..19425.00 rows=1000000 width=4) (actual time=0.006..81.434 rows=1000000 loops=1)
   Filter: ((c1 > 0) AND (c1 < 1000001))
 Planning Time: 0.110 ms
 Execution Time: 104.163 ms
(4 rows)

Time: 116.703 ms
pfs=>
pfs=> explain analyze select c2 from t1 where c1 > 0 and c1 < 2;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..19425.00 rows=1 width=4) (actual time=0.005..44.891 rows=1 loops=1)
   Filter: ((c1 > 0) AND (c1 < 2))
   Rows Removed by Filter: 999999
 Planning Time: 0.289 ms
 Execution Time: 44.902 ms
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: 1756304238, Plan Hash: 504217123, Minimum Cost Plan Hash: 26892416
(7 rows)

Time: 53.807 ms
pfs=>
pfs=> explain analyze select c2 from t1 where c1 >= 1 and c1 <= 1000000;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using i1 on t1  (cost=0.42..35408.43 rows=1000000 width=4) (actual time=0.015..153.541 rows=1000000 loops=1)
   Index Cond: ((c1 >= 1) AND (c1 <= 1000000))
 Planning Time: 0.307 ms
 Execution Time: 177.726 ms
 Note: An Approved plan was used instead of the minimum cost plan.
 SQL Hash: -2118270863, Plan Hash: 26892416, Minimum Cost Plan Hash: 504217123
(6 rows)

Time: 187.643 ms