PostgreSQL にてWHERE句で＝演算子の左辺・右辺に同じ列名を指定すると行数推定が不正確になることがある

はじめに

WHERE句で＝演算子の左辺・右辺に同じ列名を指定したクエリを考えます。

SELECT count(*) FROM seq WHERE value = value;

これは以下のクエリと等価です。

SELECT count(*) FROM seq WHERE value IS NOT NULL;

value = value で value = NULL であれば、NULL = NULL となり、これは unknown と評価されるためです。

NULL に比較述語を適用した結果が、常に unknown になってしまうからです。クエリの結果として選択されるのは、WHERE句の条件評価がtrueになる行のみです。false や unknown の行は選択されません。
引用：達人に学ぶ SQL徹底指南書 1-3 3値論理とNULL
達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)
作者:ミック
発売日: 2008/02/07
メディア: 単行本（ソフトカバー）

冒頭の2つのクエリが等価であれば実行計画も同じになりそうなものですが、PostgreSQL 10 以前では行数推定が不正確となるケースがあります。これを実例で確認します。また、比較として Oracle Database でも確認しました。

挙動検証

PostgreSQL 10

PostgreSQL 10.3 で検証を行いました。
また、これより以前のバージョンでも同様の事象を確認しました。

=# CREATE TABLE seq(value integer);

=# INSERT INTO seq(value)
-#   SELECT generate_series(1, 999);

=# INSERT INTO seq(value) VALUES (NULL);

=# vacuum analyze seq;

=# EXPLAIN SELECT count(*) FROM seq;
                         QUERY PLAN
-------------------------------------------------------------
 Aggregate  (cost=17.50..17.51 rows=1 width=8)
   ->  Seq Scan on seq  (cost=0.00..15.00 rows=1000 width=0)
(2 rows)

=# EXPLAIN SELECT count(*) FROM seq WHERE value = value;
                        QUERY PLAN
----------------------------------------------------------
 Aggregate  (cost=17.51..17.52 rows=1 width=8)
   ->  Seq Scan on seq  (cost=0.00..17.50 rows=5 width=0)
         Filter: (value = value)
(3 rows)

=# EXPLAIN SELECT count(*) FROM seq WHERE value IS NOT NULL;
                         QUERY PLAN
------------------------------------------------------------
 Aggregate  (cost=17.50..17.51 rows=1 width=8)
   ->  Seq Scan on seq  (cost=0.00..15.00 rows=999 width=0)
         Filter: (value IS NOT NULL)
(3 rows)

WHERE句無し、もしくは value IS NOT NULL のケースでは Seq Scan の行数推定(rows)の値が正確です。

ですが、value = value のケースでは Seq Scan の行数推定(rows)の値が不正確です。本来は999となるべきところが5と推定されています。これは＝演算子の左辺・右辺に同じ列名が指定されるケースをプランナが考慮していないために起こっています。

PostgreSQL 11

PostgreSQL 11.0 で検証を行いました。

=# CREATE TABLE seq(value integer);

=# INSERT INTO seq(value)
-#   SELECT generate_series(1, 999);

=# INSERT INTO seq(value) VALUES (NULL);

=# vacuum analyze seq;

=# EXPLAIN SELECT count(*) FROM seq;
                         QUERY PLAN
-------------------------------------------------------------
 Aggregate  (cost=17.50..17.51 rows=1 width=8)
   ->  Seq Scan on seq  (cost=0.00..15.00 rows=1000 width=0)
(2 rows)

testdb=# EXPLAIN SELECT count(*) FROM seq WHERE value = value;
                         QUERY PLAN
------------------------------------------------------------
 Aggregate  (cost=17.50..17.51 rows=1 width=8)
   ->  Seq Scan on seq  (cost=0.00..15.00 rows=999 width=0)
         Filter: (value IS NOT NULL)
(3 rows)

=# EXPLAIN SELECT count(*) FROM seq WHERE value IS NOT NULL;
                         QUERY PLAN
------------------------------------------------------------
 Aggregate  (cost=17.50..17.51 rows=1 width=8)
   ->  Seq Scan on seq  (cost=0.00..15.00 rows=999 width=0)
         Filter: (value IS NOT NULL)
(3 rows)

value = value のケースでも Seq Scan の行数推定(rows)の値が正確となりました。これは PostgreSQL 11 で改良されました。

Reduce var = var to var IS NOT NULL where equivalent (Tom Lane)
PostgreSQL: Documentation: 11: E.11. Release 11

改良に関する情報は以下のコミットログ及びディスカッションを参照ください。

◆ コミットログ
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8ec5429e2f422f4d570d4909507db0d4ca83bbac

◆ ディスカッション
PostgreSQL: Discussion on missing optimizations

ディスカッションのスレッドでは value = value をヒント句代わりに利用しているケースがあること、それは PostgreSQL 11 で書き換えが必要であることも述べられています。

The tautological col = col comparison on is occasionally used as a planner "hint" to correct for row count overestimates. Not a great solution, but PostgreSQL doesn't really have a better way to guide the planner. Those queries will now have to do something else, like col = col + 0, which still works.
PostgreSQL: Re: Discussion on missing optimizations

Oracle Database

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 で検証を行いました。

SQL> CREATE TABLE seq(value integer);

SQL> INSERT INTO seq(value)
SQL>   SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 999;

SQL> INSERT INTO seq(value) VALUES (NULL);

SQL> COMMIT;

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(NULL);

SQL> EXPLAIN PLAN FOR SELECT count(*) FROM seq;

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| SEQ  |  1000 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------

SQL> EXPLAIN PLAN FOR SELECT count(*) FROM seq WHERE value = value;

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| SEQ  |   999 |  3996 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
   2 - filter("VALUE" IS NOT NULL)

SQL> EXPLAIN PLAN FOR SELECT count(*) FROM seq WHERE value IS NOT NULL;

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| SEQ  |   999 |  3996 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
   2 - filter("VALUE" IS NOT NULL)

全てのケースで TABLE ACCESS FULL の Rows は正しく見積もられています。

また、刮目すべき点として、value = value のケースで "VALUE" IS NOT NULL のフィルタが適用されています。つまり、プランナは value = value が value IS NOT NULL に等しいと判断し、書き換えを行ったと推測されます。

まとめ

クエリに＝演算子の左辺・右辺に同じ列名を指定する条件 (value = value) を組み込み、結果として正しく動いたとしても、プランナを混乱させる元となります。それは、不適切な実行計画が選択され、性能劣化の一因となるかもしれません。

冒頭の説明で示した通り、value = value は value IS NOT NULL と等価であったりするので、クエリ見直しの際は value = value を単に削除するのではなく、それに伴う影響を十分に検討したうえで書き換えを行いましょう。

更新情報

2018/10/22

PostgreSQL 11 についての情報を追加しました。

2018/10/28

PostgreSQL 11 の検証結果を追加しました。また、これに伴い一部修正を行いました。

PostgreSQL 11 で改良されたことを教えてくださった @nori_shinoda さんと @tjtakahashi さんに深く感謝申し上げます。

PostgreSQL 11 では IS NOT NULL に変換されていますので、Oracle Databaseと同じ動作になっているようです。https://t.co/B654KBiEfZ
— Noriyoshi Shinoda (@nori_shinoda) 2018年10月22日

PostgreSQL 11で試したら
=# EXPLAIN SELECT count(*) FROM seq WHERE value = value;
・・・中略
Filter: (value IS NOT NULL)
となりました。
改善されている！ https://t.co/LVQsQjB5jx
— Tomoo Takahashi (@tjtakahashi) 2018年10月22日

ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中