ぱと隊長日誌

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

特定の分野を究めるとY型人材になる

先日、こんなツイートを見かけました。

これは自分の経験からもいえ、かつチームメンバーにも繰り返し話していることそのものでした。そこで、この機会にまとめておくことにします。

私は茶道を学んでいたことがあります。茶道の稽古のメインは作法や点て方ですが、毎回の稽古は床の間にある掛物(掛け軸)と花を拝見するところから始まりました。そして、掛け軸の言葉の意味や花について説明がありました。
掛け軸は書道、花は華道へとつながります。つまり、茶道を究めようとすれば、書道と華道についての理解も必要になるのですよ、という教えでした。

私はITエンジニアです。そこで例えるのであれば、データベースを究めようと思うと、データベース製品に関する知識・経験だけでなく、CPU・メモリ・ストレージ・ネットワーク・OS・アプリケーションなど、様々なレイヤーの知識と経験が求められます。

つまり、どんな分野であっても、何かを究めるには周辺分野に関する理解が必要になるということです。
ある分野を究めていけば、自分の持っている知識や経験だけでは進めなくなる地点があります。そして、自分に何が足りないのかを気づく瞬間があります。それはもしかしたら自分にとって興味のない分野かもしれません。そこに時間を割くことは回り道に見えるかもしれません。でも、それに取り組むことが、さらに進むために必要なことなのです。

〇(I,T,Π...)型人材とよく例えられますが、今回のケースでいえばY型人材といえるのではないでしょうか。Yの下半分の棒は究めようとしている分野であり、Yの上半分はそれを支える土台です。
土台をイメージするのであれば、逆Yと例えたほうがよいかもしれません。しっかりとした土台があってこそ、高みを目指すことができます。

何かを深く追求すれば、いつか壁にぶつかります。それは自分に足りないところがあるからかもしれません。そこに気が付いたのであれば、それを補う努力をすることが必要です。今の自分の範囲内で済まそうとしても、またすぐに壁にぶつかることになります。

土台を築くことは単なる回り道ではありません。そこで新たな発見があるかもしれないし、別のことを究める土台にもなりえます。そしてなにより、自分の幅が広がることになります。

自分が興味を持ったことにとことん打ち込むことで、必然的に周辺知識や経験を身につける。それはY型人材の戦略といえるのではないでしょうか。

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

はじめに

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

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

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

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

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

NULL に比較述語を適用した結果が、常に unknown になってしまうからです。クエリの結果として選択されるのは、WHERE句の条件評価がtrueになる行のみです。false や unknown の行は選択されません。
引用:達人に学ぶ SQL徹底指南書 1-3 3値論理とNULL

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

冒頭の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.1. 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 = valuevalue IS NOT NULL に等しいと判断し、書き換えを行ったと推測されます。

まとめ

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

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

更新情報

2018/10/22

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

2018/10/28

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

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

PostgreSQL のシリアライザブル分離レベルにおけるスナップショットのタイミング

PostgreSQL 10 のマニュアルには以下の記載があります。

13.2.2. リピータブルリード分離レベル
リピータブルリードのトランザクション内の問い合わせは、トランザクション内の現在の文の開始時点ではなく、トランザクションの最初のトランザクション制御以外の文の開始時点のスナップショットを見る、という点でこのレベルはリードコミッティドと異なります。 従って、単一トランザクション内の連続するSELECT文は、同じデータを参照します。つまり、自身のトランザクションが開始した後にコミットされた他のトランザクションによる変更を参照しません。

13.2.3. シリアライザブル分離レベル
実際、この分離レベルは、(ある時点で)逐次実行可能なすべてのトランザクションにおいて、シリアライザブルトランザクションの同時実行の組が一貫性のないような振る舞いをしていないか監視することを除き、リピータブルリードと全く同じ動きをします。

13.2. トランザクションの分離

この記載から、PostgreSQL のシリアライザブル分離レベルのスナップショットはリピータブルリード分離レベルと同様に「トランザクションの最初のトランザクション制御以外の文の開始時点のスナップショットを見る」ということが推測できますし、実際にそのようにふるまいます。例を挙げて確認します。

検証は PostgreSQL 10.3 で行っています。
トランザクションの略称として"TX"を用います。

(1)先行TX(TX1)のBEGIN直後に後続TX(TX2)が割り込んだ場合

-- TX1

=# CREATE TABLE valtab(value integer);

=# INSERT INTO valtab values (1);

=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- TX2

=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

=# SELECT * FROM valtab;
 value
-------
     1
(1 row)

=# UPDATE valtab SET value = 2;

=# COMMIT;
-- TX1

=# SELECT * FROM valtab;
 value
-------
     2
(1 row)

=# COMMIT;

TX1のBEGIN直後にTX2によってUPDATE/COMMITした内容を参照しました。つまり、シリアライザブル分離レベルにおいても参照するスナップショットはBEGIN時点ではないとわかります。

(2)先行TX(TX1)の操作後に後続TX(TX2)が割り込んだ場合

-- TX1

=# CREATE TABLE valtab(value integer);

=# INSERT INTO valtab values (1);

=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

=# SELECT * FROM valtab;
 value
-------
     1
(1 row)
-- TX2

=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

=# SELECT * FROM valtab;
 value
-------
     1
(1 row)

=# UPDATE valtab SET value = 2;

=# COMMIT;
-- TX1

=# SELECT * FROM valtab;
 value
-------
     1
(1 row)

=# COMMIT;

TX1のBEGIN直後のSELECT結果とTX2割り込み後のSELECT結果が同じとなっています(シリアライザブル分離レベルなので当然ではあります)。シリアライザブル分離レベルにおいても「トランザクションの最初のトランザクション制御以外の文の開始時点のスナップショットを見る」ことが分かりました。