スマートロジックの亜種のお話 / PostgreSQL, MySQL, Oracle Databaseそれぞれの影響 Tweet

本題に入る前に、

祝！　Mac De Oracle 20周年記念!!!!! Happy 20th Anniversary Mac De Oracle !!!

20年前の第一回目の記事はどんな内容だったでしょう！！！！
Panther De Oracle10g その１

MacOS (Mac OS X 10.3 / Panther ) に直接イントールして遊べる Oracle Database　があったころの話ですw
なつかしい！
また、当時は失敗作と名高いw　（今のMac Studio並の筐体）PowerMac G4 Cube 450Mhz改　Sonnet 1.2Ghz　832GMB RAM　を使っていました。これまた懐かしいSonnetのCPUカードで。

そして最後に、最も大切なブログの名前　"Mac De Oracle" は、どう閃いたのか！！！
当時、Zopeネタのブログ、 "Cube De Zope" からG4 Cube繋がりでインスパイアされて、 de だけ頂いて (^^)　、
（知ってるかたどれぐらいいるのでしょうね）

付けたタイトルが、Mac De Oracle でした。

あ、忘れちゃいけない、そもそもブログ書いたら〜。Oracleの!!
と、背中を推してくれたのは妻なんですけどねw　
彼女の一言がなかったら Mac De Oracleも Oracle ACEにもなってなかったでしょうね。
ありがとう！

ということで、

これからも雑多なネタ織り交ぜて、楽しく書いていくことになるw
Mac De Oracle (まだ残してあるIntel MacのVirtalBoxで動いてたりするネタも多いのですけどもw)　を、よろしくお願いいたします。 m(_ _)m

---

---

では、本日のおはなし。（ちょいと長めです）

WHERE句でCOALESCE関数を利用したスマートロジックの亜種って、Poor performanceを起こすってネタ、世間ではかなり書かれている印象なのですが、今日は、そんな中でも癖の強そうな、COALESCE関数を使ったスマートロジックの亜種の話。。

スマートロジックの亜種と書いた理由は、動的SQLで素直に書けばなんの問題のないもない比較的単純なSQL文を、COALESCE関数とNULLを組み合わせ動的SQLを回避したいという意図で書かれたものだから。。です。

また、この亜種は利用するデータベース（オプティマイザーだが）で影響の出方も異なるというところが、興味深いところだったりします。
なお、影響が小さそうに見える症状でも、膨大な量のデータも扱っている方達には、塵も積もれば。。。。という点には、むむむむ。。。となるはず。。

Oracle Database 21c, MySQL 8.0.36, PostgreSQL 16.3 いずれも、ちょい前のリリースにしてあります。前からそうだからってのも診てもらおうと。。。
(なお、今回利用している環境は、MacBook Intel 上のVirtualBoxのVMを利用しています。このためだけにIntel Macが残っているww)

piraruku ~ % sw_vers
ProductName:	macOS
ProductVersion:	12.7.6
BuildVersion:	21H1320
piraruku ~ % /usr/sbin/system_profiler SPHardwareDataType | grep -E '(Processor|Cores|Memory|Chip|Model Name)'
      Model Name: MacBook
      Processor Name: Dual-Core Intel Core m5
      Processor Speed: 1.2 GHz
      Number of Processors: 1
      Total Number of Cores: 2
      Memory: 8 GB
piraruku ~ % VBoxManage -V
7.0.10r158379

Oracle Databaseの表と索引、データ件数ですが、MySQL, PostgreSQLでも同一の表、索引、データを登録してあります。

SCOTT@orclpdb1> desc customers
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------------
 CUSTOMER_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                NOT NULL VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(20)
 ADDRESS                                            VARCHAR2(40)
 PHONE_NUMBER                                       VARCHAR2(25)

SCOTT@orclpdb1> desc orders
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------------
 ORDER_ID                                  NOT NULL NUMBER(12)
 ORDER_DATE                                NOT NULL TIMESTAMP(6) WITH LOCAL TIME　ZONE
 ORDER_MODE                                         VARCHAR2(8)
 CUSTOMER_ID                               NOT NULL NUMBER(6)
 ORDER_STATUS                                       NUMBER(2)
 ORDER_TOTAL                                        NUMBER(8,2)
 SALES_REP_ID                                       NUMBER(6)
 PROMOTION_ID                                       NUMBER(6)


 TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
 ------------------------------ ------------------------------ ------------------------------
 CUSTOMERS                      PK_CUSTOMERS                   CUSTOMER_ID
 ORDERS                         FK_ORDERS_CUSTOMERS            CUSTOMER_ID
 ORDERS                         PK_ORDERS                      ORDER_ID

SCOTT@orclpdb1> select count(1) from orders;

  COUNT(1)
----------
       105

SCOTT@orclpdb1> select count(1) from customers;

  COUNT(1)
----------
       319

まずは、問題のSQL文の例から診てもらいましょう。

SELECT
  orders.order_id
  , orders.order_date
  , customers.first_name
FROM
  orders
  INNER JOIN customers
  ON
    orders.customer_id = customers.customer_id
WHERE
  customers.customer_id = COALESCE(:cust_id, customers.customer_id)
  AND orders.order_id = COALESCE(:order_id, orders.order_id);

みなさん, 上記SQL文のWHERE句で、なにをスマートにやりたい（と思っている）のか、わかりますか？

答え

バインド変数（cust_id, order_id）にどちも NULL がセットされた場合は、全顧客のオーダーをリスト.
特定のcust_idが指定されたら、該当顧客の前オーダーをリスト(このときorder_idはNULL).
特定のorder_idが指定された、該当オーダーをリスト（このときcust_idはNULL).
どちらの変数にも特定のオーダーと顧客が指定されたら該当するオーダーをリスト。

ようするに、全件取得と特定のデータの取得で個別のWHEREの条件に書きかける動的SQLにすればよいわけですけども、
動的SQLを回避したい、ただそこだけに着目してしまった"スマートロジックの亜種"になっています。

WHERE句のCOALESCE関数。SELECTリストならともかく、検索時の弊害が多くなる使い方ですよね。
スマートなように見えているのは机の上だけです。

実際にオプティマイザがどうするか次第ですが、大抵の場合、検索時のフィルタリングが諸悪の根源になる場合が多いいですよね。ご存知の方も多いと思います。
（データ量が多い場合は軽視できない部分です。それらについてもググると沢山ヒットするはずです）

で、
Oracle Database/MySQL/PostgreSQLのうち一つで、フィルタリング以外の影響を引き起こすことに気がついてしまったので、メモがわりに書いておきまっす”

(今回利用したスクリプトは後半に貼ってあります)

---

Oracle Database 21c から診てみましょう。

SCOTT@orclpdb1> select banner_legacy from v$version;

BANNER_LEGACY
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

1) :cust_id, :order_id どちらも NULL にした場合（なお、バインドピークは有効のままです）

事前の想定通り、全データ取得のケースであるにもかかわらず、Table full scannの操作で、COALESCE関数を伴うフィルタリングが現れています。明らかに電力の無駄遣いw　データが少ないと影響は見えにくいですが。
(HASH JOINで利用されている赤字にしている作業用メモリサイズは覚えておいてくださいね。改善後の実行計画の部分で必要になります）

SCOTT@orclpdb1> @testrun1 null null

   CUST_ID
----------
[null]

  ORDER_ID
----------
[null]

  1  SELECT
  2    /*+
  3      gather_plan_statistics
  4    */
  5    orders.order_id
  6    , orders.order_date
  7    , customers.first_name
  8  FROM
  9    orders
 10    INNER JOIN customers
 11    ON
 12      orders.customer_id = customers.customer_id
 13  WHERE
 14    customers.customer_id = COALESCE(:cust_id, customers.customer_id)
 15*   AND orders.order_id = COALESCE(:order_id, orders.order_id)

...略...

Plan hash value: 23084738

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |     6 (100)|    105 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN         |           |      1 |     6   (0)|    105 |00:00:00.01 |      13 |  1376K|  1376K| 1558K (0)|
|*  2 |   TABLE ACCESS FULL| ORDERS    |      1 |     5   (0)|    105 |00:00:00.01 |       2 |       |       |          |
|*  3 |   TABLE ACCESS FULL| CUSTOMERS |      1 |     1   (0)|    319 |00:00:00.01 |      11 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

...略...

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ORDERS"."CUSTOMER_ID"="CUSTOMERS"."CUSTOMER_ID")
   2 - filter("ORDERS"."ORDER_ID"=COALESCE(:ORDER_ID,"ORDERS"."ORDER_ID"))
   3 - filter("CUSTOMERS"."CUSTOMER_ID"=COALESCE(:CUST_ID,"CUSTOMERS"."CUSTOMER_ID"))

なお、opt_param('_optim_peek_user_binds', 'false')ヒントでbind peekをオフにしても同じ結果でした。

2) :cust_id, :order_idともに値をNULL以外に設定した場合（バインドピーク有効のままですがハードパースさせています）

実行計画が変化しました。ただ、ORDERSではTable Full Scanのままです。(データ量が少ない影響かもしれないですね。とは言え気になるw）

SCOTT@orclpdb1> @testrun1 144 2435

   CUST_ID
----------
       144

  ORDER_ID
----------
      2435

...略...

Plan hash value: 4191309613

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |      1 |        |       |     6 (100)|      1 |00:00:00.01 |       4 |
|   1 |  NESTED LOOPS                |              |      1 |      1 |    30 |     6   (0)|      1 |00:00:00.01 |       4 |
|   2 |   NESTED LOOPS               |              |      1 |      1 |    30 |     6   (0)|      1 |00:00:00.01 |       3 |
|*  3 |    TABLE ACCESS FULL         | ORDERS       |      1 |      1 |    19 |     5   (0)|      1 |00:00:00.01 |       2 |
|*  4 |    INDEX UNIQUE SCAN         | PK_CUSTOMERS |      1 |      1 |       |     0   (0)|      1 |00:00:00.01 |       1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |      1 |      1 |    11 |     1   (0)|      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------------------------------------

...略...

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ORDERS"."ORDER_ID"=COALESCE(:ORDER_ID,"ORDERS"."ORDER_ID"))
   4 - access("ORDERS"."CUSTOMER_ID"="CUSTOMERS"."CUSTOMER_ID")
      filter("CUSTOMERS"."CUSTOMER_ID"=COALESCE(:CUST_ID,"CUSTOMERS"."CUSTOMER_ID"))

ORDERSの主キー索引をユニークスキャンさせるようにindexヒントを追加すると素直に効いてくれますね。ですが、ここがポイントです。index full scanしています。

ここ試験にでますよ。これCOALESCE関数の影響です。主キー索引からindex unique scanすればよいはずですが、できななくなるんですよ。

index full scan + table access by index rowid batched　(１行だけ) ですが. 結局、無駄なデータにアクセスして捨てていることになりますね。ほとんどを。これじゃダメですよね。
実行計画は、全くスマートじゃなくなっていますww

SCOTT@orclpdb1> @testrun1 144 2435

...略...

  1  SELECT
  2    /*+
  3      gather_plan_statistics
  4      index(orders pk_orders)
  5    */
  6    orders.order_id

...略...

Plan hash value: 109917740

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |     3 (100)|      1 |00:00:00.01 |       4 |
|   1 |  NESTED LOOPS                         |              |      1 |     3   (0)|      1 |00:00:00.01 |       4 |
|   2 |   NESTED LOOPS                        |              |      1 |     3   (0)|      1 |00:00:00.01 |       3 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORDERS       |      1 |     2   (0)|      1 |00:00:00.01 |       2 |
|*  4 |     INDEX FULL SCAN                   | PK_ORDERS    |      1 |     1   (0)|      1 |00:00:00.01 |       1 |
|*  5 |    INDEX UNIQUE SCAN                  | PK_CUSTOMERS |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | CUSTOMERS    |      1 |     1   (0)|      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------------------------

...略...

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("ORDERS"."ORDER_ID"=COALESCE(:ORDER_ID,"ORDERS"."ORDER_ID"))
   5 - access("ORDERS"."CUSTOMER_ID"="CUSTOMERS"."CUSTOMER_ID")
       filter("CUSTOMERS"."CUSTOMER_ID"=COALESCE(:CUST_ID,"CUSTOMERS"."CUSTOMER_ID"))

3) 1)を動的SQLで回避した場合、全件取得はWHERE句のない文になりますよね！

最適な実行計画にするために動的SQL化し生成されたSQL文を実行して1)のケースがどう改善されるか診てみましょう

全行取得するのでWHERE句は不要。シンプルになりました。
余計なフィルタリングも消えました。

この例だとMERGE JOINになっていますが、データ量が少ない影響だと推測。
ソートに使うメモリを索引を使って回避している動きがあることから利用するメモリサイズが少なくなるためでしょうね。データ量が大きくなるとHash Joinに切り替わるかもしれないですね。
（理由は、1)の実行計画のHASH JOINが利用するメモリサイズとの比較で小さくなっていることがわかります）

  1  SELECT
  2      /*+
  3        gather_plan_statistics
  4      */
  5      orders.order_id
  6      , orders.order_date
  7      , customers.first_name
  8    FROM
  9      orders
 10      INNER JOIN customers
 11      ON
 12*       orders.customer_id = customers.customer_id

...略...

Plan hash value: 3079428679

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Starts | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |      1 |     9 (100)|    105 |00:00:00.01 |      20 |       |       |          |
|   1 |  MERGE JOIN                  |                     |      1 |     9  (12)|    105 |00:00:00.01 |      20 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| ORDERS              |      1 |     2   (0)|    105 |00:00:00.01 |      16 |       |       |          |
|   3 |    INDEX FULL SCAN           | FK_ORDERS_CUSTOMERS |      1 |     1   (0)|    105 |00:00:00.01 |       8 |       |       |          |
|*  4 |   SORT JOIN                  |                     |    105 |     7  (15)|    105 |00:00:00.01 |       4 | 20480 | 20480 |18432  (0)|
|   5 |    TABLE ACCESS FULL         | CUSTOMERS           |      1 |     6   (0)|    319 |00:00:00.01 |       4 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------

...略...

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ORDERS"."CUSTOMER_ID"="CUSTOMERS"."CUSTOMER_ID")
       filter("ORDERS"."CUSTOMER_ID"="CUSTOMERS"."CUSTOMER_ID")
 

4) 2)の改善

動的SQL化によりCOALESCE関数は不要でわかりやすいSQL文ですよね。こちらのほうが自然ですw

見ての通り、WHERE句に記述されているCOALESCE関数を含むフィルタリングが消え、スッキリしたPredicate Informationの内容に変化しました。これで安心！

  1  SELECT
  2      /*+
  3        gather_plan_statistics
  4      */
  5      orders.order_id
  6      , orders.order_date
  7      , customers.first_name
  8    FROM
  9      orders
 10      INNER JOIN customers
 11      ON
 12        orders.customer_id = customers.customer_id WHERE
 13             customers.customer_id = :cust_id
 14*            AND orders.order_id = :order_id

...略...

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Starts | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |      1 |     2 (100)|      1 |00:00:00.01 |       4 |
|   1 |  NESTED LOOPS                |              |      1 |     2   (0)|      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN         | PK_CUSTOMERS |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| ORDERS       |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  5 |    INDEX UNIQUE SCAN         | PK_ORDERS    |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------

...略...

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CUSTOMERS"."CUSTOMER_ID"=:CUST_ID)
   4 - filter("ORDERS"."CUSTOMER_ID"=:CUST_ID)
   5 - access("ORDERS"."ORDER_ID"=:ORDER_ID)
 

次は、MySQL 8.0.36

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.36    |
+-----------+
 

1) :cust_id, :order_id どちらも NULL にした場合

Oracle Databaseでの症状同様に、無駄なフィルタリングが現れていますよね

mysql> set @cust_id = null;
Query OK, 0 rows affected (0.00 sec)

mysql> set @order_id = null;
Query OK, 0 rows affected (0.00 sec)

mysql> select @cust_id;
+--------------------+
| @cust_id           |
+--------------------+
| NULL               |
+--------------------+v1 row in set (0.00 sec)

mysql> select @order_id;
+----------------------+
| @order_id            |
+----------------------+
| NULL                 |
+----------------------+
1 row in set (0.01 sec)

mysql> explain format=tree
    -> SELECT
    ->   orders.order_id
    ->   , orders.order_date
    ->   , customers.first_name
    -> FROM
    ->   orders
    ->   INNER JOIN customers
    ->   ON
    ->     orders.customer_id = customers.customer_id
    -> WHERE
    ->   customers.customer_id = COALESCE(@cust_id, customers.customer_id)
    ->   AND orders.order_id = COALESCE(@order_id, orders.order_id);
+-----------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=13.6 rows=10.5)
    -> Filter: ((cast(orders.customer_id as double) = cast(coalesce(((@cust_id)),orders.customer_id) as double)) 
       and (cast(orders.order_id as double) = cast(coalesce(((@order_id)),orders.order_id) as double)))  (cost=2.05 rows=10.5)
        -> Table scan on orders  (cost=2.05 rows=105)
    -> Single-row index lookup on customers using PRIMARY (customer_id=orders.customer_id)  (cost=1.01 rows=1)
 |
+-----------------------------------------------------------------------------------------------------------+

2):cust_id, :order_idねともに値をNULL以外に設定した場合

このケースもOracle Databaseでの症状に類ていますよね。ふむふむ

mysql> set @cust_id = 144;
Query OK, 0 rows affected (0.00 sec)

mysql> set @order_id = 2435;
Query OK, 0 rows affected (0.00 sec)

mysql> select @cust_id;
+----------+
| @cust_id |
+----------+
|      144 |
+----------+
1 row in set (0.00 sec)

mysql> select @order_id;
+-----------+
| @order_id |
+-----------+
|      2435 |
+-----------+
1 row in set (0.00 sec)

mysql> explain format=tree
    -> SELECT
    ->   orders.order_id
    ->   , orders.order_date
    ->   , customers.first_name
    -> FROM
    ->   orders
    ->   INNER JOIN customers
    ->   ON
    ->     orders.customer_id = customers.customer_id
    -> WHERE
    ->   customers.customer_id = COALESCE(@cust_id, customers.customer_id)
    ->   AND orders.order_id = COALESCE(@order_id, orders.order_id);
+-----------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=13.6 rows=10.5)
    -> Filter: ((orders.customer_id = coalesce(((@cust_id)),orders.customer_id)) 
        and (orders.order_id = coalesce(((@order_id)),orders.order_id)))  (cost=2.05 rows=10.5)
        -> Table scan on orders  (cost=2.05 rows=105)
    -> Single-row index lookup on customers using PRIMARY (customer_id=orders.customer_id)  (cost=1.01 rows=1)
 |
+-----------------------------------------------------------------------------------------------------------+

3) 1)を動的SQLにして適切なSQLになった対策後の結果

そもそも全行取得するのになんでフィルタリングさせちゃうようなWHERE句を書くのかと。。
フィルタリングはなくなり綺麗なものです。

mysql> explain format=tree
    -> SELECT
    ->   orders.order_id
    ->   , orders.order_date
    ->   , customers.first_name
    -> FROM
    ->   orders
    ->   INNER JOIN customers
    ->   ON
    ->     orders.customer_id = customers.customer_id;
+-----------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=127 rows=105)
    -> Table scan on orders  (cost=11.5 rows=105)
    -> Single-row index lookup on customers using PRIMARY (customer_id=orders.customer_id)  (cost=1 rows=1)
 |
+-----------------------------------------------------------------------------------------------------------+

4) 3)同様に動的SQLにして、COALESCE関数を排除した結果。

無駄なフルタリングも一歳なし。こうでなくちゃw COAESCE関数を含むフィルタなんていらんのですよ。

mysql> set @cust_id = 144;
Query OK, 0 rows affected (0.00 sec)

mysql> set @order_id = 2435;
Query OK, 0 rows affected (0.00 sec)

mysql> select @cust_id;
+----------+
| @cust_id |
+----------+
|      144 |
+----------+
1 row in set (0.00 sec)

mysql> select @order_id;
+-----------+
| @order_id |
+-----------+
|      2435 |
+-----------+
1 row in set (0.00 sec)

mysql> explain format=tree
    -> SELECT
    ->   orders.order_id
    ->   , orders.order_date
    ->   , customers.first_name
    -> FROM
    ->   orders
    ->   INNER JOIN customers
    ->   ON
    ->     orders.customer_id = customers.customer_id
    -> WHERE
    ->   customers.customer_id = @cust_id
    ->   AND orders.order_id = @order_id;
+-------------------------------------------------------+
| EXPLAIN                                               |
+-------------------------------------------------------+
| -> Rows fetched before execution  (cost=0..0 rows=1)
 |
+-------------------------------------------------------+

そして、最後は、PostgreSQL 16.3　

とりを務めているぐらいなのでw、
Oracle Database, MySQLとはちょっと違った影響がありそうなのは、気づいている方が多そう。。。。
それがどのような違いなのか診てみましょう。

perftestdb=> select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit
(1 行)

1) :cust_id, :order_id どちらも NULL にした場合

全行取得してINNER JOINするだけですが、Join Cardinarityが変です！！！！！

Rows Removed by Join Filter: 3129とあります。105rows*31rowsなので、なぜか直積しちゃってますね。わぉ！　細かいところだが一部のROWSなんとなく合わないようなきもするが、そこは見てないことにしておこう。。 フィルタリングが余計なのはOracle Database, MySQLと同じ。

症状としては重症の類ですよね。

データが多くなった場合の直積はフィルタリング以上にキツイですよーーーーっ！

perftestdb=> \pset null [null]
Null表示は"[null]"です。
perftestdb=> \set cust_id NULL
perftestdb=> \set order_id NULL
perftestdb=> \echo :cust_id
NULL
perftestdb=> \echo :order_id
NULL
perftestdb=> explain (analyze,verbose)
perftestdb-> SELECT
perftestdb->   orders.order_id
perftestdb->   , orders.order_date
perftestdb->   , customers.first_name
perftestdb-> FROM
perftestdb->   orders
perftestdb->   INNER JOIN customers
perftestdb->   ON
perftestdb->     orders.customer_id = customers.customer_id
perftestdb-> WHERE
perftestdb->   customers.customer_id = COALESCE(:cust_id, customers.customer_id)
perftestdb->   AND orders.order_id = COALESCE(:order_id, orders.order_id);

                                               QUERY PLAN                                 
--------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..10.33 rows=1 width=18) (actual time=0.025..1.416 rows=105 loops=1)
   Output: orders.order_id, orders.order_date, customers.first_name
   Inner Unique: true
   Join Filter: (orders.customer_id = customers.customer_id)
    Rows Removed by Join Filter: 3129
   ->  Seq Scan on public.orders  (cost=0.00..2.31 rows=1 width=16) (actual time=0.016..0.041 rows=105 loops=1)
         Output: orders.order_id, orders.order_date, orders.order_mode, orders.customer_id
                 , orders.order_status, orders.order_total, orders.sales_rep_id, orders.promotion_id
          Filter: (orders.order_id = COALESCE(orders.order_id))
   ->  Seq Scan on public.customers  (cost=0.00..7.99 rows=2 width=10) (actual time=0.001..0.007 rows=31 loops=105)
         Output: customers.customer_id, customers.first_name
                 , customers.last_name, customers.address, customers.phone_number
          Filter: (customers.customer_id = COALESCE(customers.customer_id))
 Planning Time: 0.456 ms
 Execution Time: 1.453 ms

2):cust_id, :order_idともに値をNULL以外に設定した場合

主キーを利用するかと思いきや、COALESCE関数の影響で全表走査してフィルタリングしてほぼ捨ててますね なぜ(???)

perftestdb=> \pset null [null]
Null表示は"[null]"です。
perftestdb=> \set cust_id 144
perftestdb=> \set order_id 2435
perftestdb=> \echo :cust_id
144
perftestdb=> \echo :order_id
2435
perftestdb=> explain (analyze,verbose)
SELECT
  orders.order_id
  , orders.order_date
  , customers.first_name
FROM
  orders
  INNER JOIN customers
  ON
    orders.customer_id = customers.customer_id
WHERE
  customers.customer_id = COALESCE(:cust_id, customers.customer_id)
  AND orders.order_id = COALESCE(:order_id, orders.order_id);
  
                                             QUERY PLAN                                                
--------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..10.57 rows=1 width=18) (actual time=0.062..0.155 rows=1 loops=1)
   Output: orders.order_id, orders.order_date, customers.first_name
   ->  Seq Scan on public.orders  (cost=0.00..2.58 rows=1 width=16) 
                                                              (actual time=0.038..0.064 rows=1 loops=1)
         Output: orders.order_id, orders.order_date, orders.order_mode, orders.customer_id
                 , orders.order_status, orders.order_total, orders.sales_rep_id, orders.promotion_id
         Filter: ((orders.customer_id = 144) AND (orders.order_id = 2435))
         Rows Removed by Filter: 104
   ->  Seq Scan on public.customers  (cost=0.00..7.99 rows=1 width=10) 
                                                                 (actual time=0.020..0.085 rows=1 loops=1)
         Output: customers.customer_id, customers.first_name
                 , customers.last_name, customers.address, customers.phone_number
         Filter: (customers.customer_id = 144)
         Rows Removed by Filter: 318
 Planning Time: 0.253 ms
 Execution Time: 0.225 ms

ちょいと気になるのでヒントでどうなるか追加で診ておきましょう。このケースでは改善。
pg_hint_planで索引スキャンでよりよくできますね。（データ量の影響だろうか。。）

perftestdb=> explain (analyze,verbose)
SELECT
  /*+
    IndexScan(orders pk_orders)
    IndexScan(customers pk_customers)
  */
  orders.order_id
  , orders.order_date
  , customers.first_name
FROM
  orders
  INNER JOIN customers
  ON
    orders.customer_id = customers.customer_id
WHERE
  customers.customer_id = COALESCE(:cust_id, customers.customer_id)
  AND orders.order_id = COALESCE(:order_id, orders.order_id);
  
                                             QUERY PLAN                                                    
--------------------------------------------------------------------------------------------------------------
  Nested Loop  (cost=0.29..16.34 rows=1 width=18) (actual time=0.039..0.043 rows=1 loops=1)
   Output: orders.order_id, orders.order_date, customers.first_name
   ->  Index Scan using pk_orders on public.orders  
              (cost=0.14..8.16 rows=1 width=16) (actual time=0.027..0.028 rows=1 loops=1)
         Output: orders.order_id, orders.order_date, orders.order_mode, orders.customer_id
                 , orders.order_status, orders.order_total, orders.sales_rep_id, orders.promotion_id
         Index Cond: (orders.order_id = 2435)
         Filter: (orders.customer_id = 144)
   ->  Index Scan using pk_customers on public.customers  
              (cost=0.15..8.17 rows=1 width=10) (actual time=0.008..0.010 rows=1 loops=1)
         Output: customers.customer_id, customers.first_name, customers.last_name
                 , customers.address, customers.phone_number
         Index Cond: (customers.customer_id = 144)
 Planning Time: 0.290 ms
 Execution Time: 0.083 ms

3) 1)の対策後。動的SQLにしてWHERE句を除外するだけです

無駄なWHERE句が消えて、スッキリ。こちらもOracle Databaseの改善後に類似したMerge Joinになってますね。直積が無くなってますねーーーー

perftestdb=> \pset null [null]
Null表示は"[null]"です。
perftestdb=> \set cust_id NULL
perftestdb=> \set order_id NULL
perftestdb=> \echo :cust_id
NULL
perftestdb=> \echo :order_id
NULL
perftestdb=> 
perftestdb=> explain (analyze,verbose)
perftestdb-> SELECT
perftestdb->   orders.order_id
perftestdb->   , orders.order_date
perftestdb->   , customers.first_name
perftestdb-> FROM
perftestdb->   orders
perftestdb->   INNER JOIN customers
perftestdb->   ON
perftestdb->     orders.customer_id = customers.customer_id
perftestdb-> ;
                                          QUERY PLAN                                     
-----------------------------------------------------------------------------------------------------
 Merge Join  (cost=5.72..11.81 rows=105 width=18) (actual time=0.116..0.240 rows=105 loops=1)
   Output: orders.order_id, orders.order_date, customers.first_name
   Merge Cond: (customers.customer_id = orders.customer_id)
   ->  Index Scan using pk_customers on public.customers  (cost=0.15..19.93 rows=319 width=10) 
                                                          (actual time=0.012..0.040 rows=71 loops=1)
         Output: customers.customer_id, customers.first_name
                 , customers.last_name, customers.address, customers.phone_number
   ->  Sort  (cost=5.57..5.84 rows=105 width=16) (actual time=0.098..0.118 rows=105 loops=1)
         Output: orders.order_id, orders.order_date, orders.customer_id
         Sort Key: orders.customer_id
         Sort Method: quicksort  Memory: 29kB
         ->  Seq Scan on public.orders  (cost=0.00..2.05 rows=105 width=16) 
                                        (actual time=0.012..0.047 rows=105 loops=1)
               Output: orders.order_id, orders.order_date, orders.customer_id
 Planning Time: 0.324 ms
 Execution Time: 0.289 ms

4) 3)と同様の対策。動的SQLにしてCOALESCE関数を利用しない。一般的な条件を付加するだけです。

データ少ないからか、Seq Scanのままですね。
まじかw

perftestdb=> \pset null [null]
Null表示は"[null]"です。
perftestdb=> \set cust_id 144
perftestdb=> \set order_id 2435
perftestdb=> \echo :cust_id
144
perftestdb=> \echo :order_id
2435
perftestdb=> explain (analyze,verbose)
perftestdb-> SELECT
perftestdb->   orders.order_id
perftestdb->   , orders.order_date
perftestdb->   , customers.first_name
perftestdb-> FROM
perftestdb->   orders
perftestdb->   INNER JOIN customers
perftestdb->   ON
perftestdb->     orders.customer_id = customers.customer_id
perftestdb-> WHERE
perftestdb->   customers.customer_id = :cust_id
perftestdb->   AND orders.order_id = :order_id;

                                               QUERY PLAN                                                    
----------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..10.57 rows=1 width=18) (actual time=0.037..0.097 rows=1 loops=1)
   Output: orders.order_id, orders.order_date, customers.first_name
   ->  Seq Scan on public.orders  (cost=0.00..2.58 rows=1 width=16) 
                                                             (actual time=0.022..0.039 rows=1 loops=1)
         Output: orders.order_id, orders.order_date, orders.order_mode, orders.customer_id
         　　　　　, orders.order_status, orders.order_total, orders.sales_rep_id, orders.promotion_id
         Filter: ((orders.customer_id = 144) AND (orders.order_id = 2435))
         Rows Removed by Filter: 104
   ->  Seq Scan on public.customers  (cost=0.00..7.99 rows=1 width=10) 
                                                                (actual time=0.011..0.053 rows=1 loops=1)
         Output: customers.customer_id, customers.first_name, customers.last_name
         　　　　　, customers.address, customers.phone_number
         Filter: (customers.customer_id = 144)
         Rows Removed by Filter: 318
 Planning Time: 0.199 ms
 Execution Time: 0.132 ms

pg_hint_planでIndexScanを強制してみましょう。。すんなり改善。
プランナーの判断だと索引使ってくれなかったのですが、ヒントで改善できる状況は確認。状況に応じて人がアシストしてあげるのがいいですかね。

perftestdb=> explain (analyze,verbose)
perftestdb-> SELECT
perftestdb->   /*+
perftestdb*>     IndexScan(orders pk_orders)
perftestdb*>     IndexScan(customers pk_customers)
perftestdb*>   */
perftestdb->   orders.order_id
perftestdb->   , orders.order_date
perftestdb->   , customers.first_name
perftestdb-> FROM
perftestdb->   orders
perftestdb->   INNER JOIN customers
perftestdb->   ON
perftestdb->     orders.customer_id = customers.customer_id
perftestdb-> WHERE
perftestdb->   customers.customer_id = :cust_id
perftestdb->   AND orders.order_id = :order_id;

                                                               QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.29..16.34 rows=1 width=18) (actual time=0.039..0.043 rows=1 loops=1)
   Output: orders.order_id, orders.order_date, customers.first_name
   ->  Index Scan using pk_orders on public.orders  (cost=0.14..8.16 rows=1 width=16) 
                                                                               (actual time=0.027..0.028 rows=1 loops=1)
         Output: orders.order_id, orders.order_date, orders.order_mode, orders.customer_id
         　　　　　, orders.order_status, orders.order_total, orders.sales_rep_id, orders.promotion_id
         Index Cond: (orders.order_id = 2435)
         Filter: (orders.customer_id = 144)
   ->  Index Scan using pk_customers on public.customers  (cost=0.15..8.17 rows=1 width=10)  
                                                                                     (actual time=0.008..0.010 rows=1 loops=1)
         Output: customers.customer_id, customers.first_name, customers.last_name
         　　　　　, customers.address, customers.phone_number
         Index Cond: (customers.customer_id = 144)
 Planning Time: 0.290 ms
 Execution Time: 0.083 ms
(11 行)

まとめると、
当初の想定通りいいことは全くない！！　スマートなやりかたに見えているだけでしたよね
COALESCE関数をWHERE句で利用したスマートロジックはかなりリスキー。

今回試した全てのケースで、余分なフィルタリングが常に行われたりするリスクに加え、
PostgreSQLでは直積まで発生しちゃいました。やばいです。

ということで、

Mac De Oracle ブログ開設 20周年記念のエントリー。おわり。

では、まだ。

Enjoy SQLs! and Optimizers!

---

==== サンプルコード ====
Oracle Database側で簡易的に動的SQL化して生成した全件取得、キー指定版のSQLをPostgreSQL/MySQLで再利用しました。

Oracle database

SCOTT@orclpdb1> !cat testrun1.sql
set termout on
variable cust_id number
variable order_id number
set autop on 
set null [null]
exec :cust_id := &1
exec :order_id := &2

SELECT
  /*+
    gather_plan_statistics
  */
  orders.order_id
  , orders.order_date
  , customers.first_name
FROM
  orders
  INNER JOIN customers
  ON
    orders.customer_id = customers.customer_id
WHERE
  customers.customer_id = COALESCE(:cust_id, customers.customer_id)
  AND orders.order_id = COALESCE(:order_id, orders.order_id)
.
l
set termout off
/
set termout on
@show_actualplan.sql

-- 簡易的にSQL*PLus内で動的SQLを組み立てて実行
set feed off
set timi off
set head off
set termout off
set veri off
spool temp_sql.sql
SELECT 
  'SELECT
    /*+
      gather_plan_statistics
    */
    orders.order_id
    , orders.order_date
    , customers.first_name
  FROM
    orders
    INNER JOIN customers
    ON
      orders.customer_id = customers.customer_id '
  ||
  CASE 
    WHEN 
      :cust_id IS NOT NULL 
      AND :order_id IS NOT NULL
    THEN
        'WHERE
           customers.customer_id = :cust_id
           AND orders.order_id = :order_id'
  END
FROM
 dual;
select '.' from dual;
select 'l' from dual;
select 'set termout off' from dual;
select '/' from dual;
select 'set termout on' from dual;
select '@show_actualplan.sql' from dual;
spo off
set feed on
set timi on
set head on
set termout on
set veri on
@temp_sql.sql

undefine 1
undefine 2
undefine cust_id
undefine order_id

show_actualplan.sql

set long 20000
set longchunk 400
select * from table(dbms_xplan.display_cursor(format=>'ALL ALLSTATS LAST +OUTLINE'));

MySQL

set @cust_id = null;
set @order_id = null;
select @cust_id;
select @order_id;


--スマートロジック版（すまーとじゃないけど）
explain format=tree
SELECT
  orders.order_id
  , orders.order_date
  , customers.first_name
FROM
  orders
  INNER JOIN customers
  ON
    orders.customer_id = customers.customer_id
WHERE
  customers.customer_id = COALESCE(@cust_id, customers.customer_id)
  AND orders.order_id = COALESCE(@order_id, orders.order_id);


--動的SQL（生成済み全件取得のSQL)
explain format=tree
SELECT
  orders.order_id
  , orders.order_date
  , customers.first_name
FROM
  orders
  INNER JOIN customers
  ON
    orders.customer_id = customers.customer_id;
    

set @cust_id = 144;
set @order_id = 2435;
select @cust_id;
select @order_id;


--スマートロジック版（すまーとじゃないけど）
explain format=tree
SELECT
  orders.order_id
  , orders.order_date
  , customers.first_name
FROM
  orders
  INNER JOIN customers
  ON
    orders.customer_id = customers.customer_id
WHERE
  customers.customer_id = COALESCE(@cust_id, customers.customer_id)
  AND orders.order_id = COALESCE(@order_id, orders.order_id);


--動的SQL（生成済みキー指定のSQL)
explain format=tree
SELECT
  orders.order_id
  , orders.order_date
  , customers.first_name
FROM
  orders
  INNER JOIN customers
  ON
    orders.customer_id = customers.customer_id
WHERE
  customers.customer_id = @cust_id
  AND orders.order_id = @order_id;

PostgreSQL

\pset null [null]
\set cust_id NULL
\set order_id NULL
\echo :cust_id
\echo :order_id

--スマートロジック版（すまーとじゃないけど）
explain (analyze,verbose)
SELECT
  orders.order_id
  , orders.order_date
  , customers.first_name
FROM
  orders
  INNER JOIN customers
  ON
    orders.customer_id = customers.customer_id
WHERE
  customers.customer_id = COALESCE(:cust_id, customers.customer_id)
  AND orders.order_id = COALESCE(:order_id, orders.order_id);
  

--動的SQL（生成済み全件取得のSQL)
explain (analyze,verbose)
SELECT
  orders.order_id
  , orders.order_date
  , customers.first_name
FROM
  orders
  INNER JOIN customers
  ON
    orders.customer_id = customers.customer_id
;


\pset null [null]
\set cust_id 144
\set order_id 2435
\echo :cust_id
\echo :order_id


--スマートロジック版（すまーとじゃないけど）
explain (analyze,verbose)
SELECT
  orders.order_id
  , orders.order_date
  , customers.first_name
FROM
  orders
  INNER JOIN customers
  ON
    orders.customer_id = customers.customer_id
WHERE
  customers.customer_id = COALESCE(:cust_id, customers.customer_id)
  AND orders.order_id = COALESCE(:order_id, orders.order_id);


--スマートロジック版（すまーとじゃないけど、キー指定版、SQL hint付き）
explain (analyze,verbose)
SELECT
  /*+
    IndexScan(orders pk_orders)
    IndexScan(customers pk_customers)
  */
  orders.order_id
  , orders.order_date
  , customers.first_name
FROM
  orders
  INNER JOIN customers
  ON
    orders.customer_id = customers.customer_id
WHERE
  customers.customer_id = COALESCE(:cust_id, customers.customer_id)
  AND orders.order_id = COALESCE(:order_id, orders.order_id);
   

--動的SQL（生成済みキー指定のSQL、SQL hint付き)
explain (analyze,verbose)
SELECT
  /*+
    IndexScan(orders pk_orders)
    IndexScan(customers pk_customers)
  */
  orders.order_id
  , orders.order_date
  , customers.first_name
FROM
  orders
  INNER JOIN customers
  ON
    orders.customer_id = customers.customer_id
WHERE
  customers.customer_id = :cust_id
  AND orders.order_id = :order_id;


--動的SQL（生成済みキー指定のSQL)
explain (analyze,verbose)
SELECT
  orders.order_id
  , orders.order_date
  , customers.first_name
FROM
  orders
  INNER JOIN customers
  ON
    orders.customer_id = customers.customer_id
WHERE
  customers.customer_id = :cust_id
  AND orders.order_id = :order_id;