OracleDB の実行計画と PostgreSQL の実行計画を見比べる⭕🐘

OracleDB / PostgreSQL 双方の DB に同じデータを用意し、同じ SQL を実行して実行計画を見比べてみます。

前提

PostgreSQL は GCP の VMインスタンスに V17 をインストールして使用しました。
データは pgbench で作成しました。(スケールファクタ = 100)

オブジェクト情報/PostgreSQL

postgres=# \d pgbench_tellers
              Table "public.pgbench_tellers"
  Column  |     Type      | Collation | Nullable | Default 
----------+---------------+-----------+----------+---------
 tid      | integer       |           | not null | 
 bid      | integer       |           |          | 
 tbalance | integer       |           |          | 
 filler   | character(84) |           |          | 
Indexes:
    "pgbench_tellers_pkey" PRIMARY KEY, btree (tid)

postgres=# \d pgbench_branches
              Table "public.pgbench_branches"
  Column  |     Type      | Collation | Nullable | Default 
----------+---------------+-----------+----------+---------
 bid      | integer       |           | not null | 
 bbalance | integer       |           |          | 
 filler   | character(88) |           |          | 
Indexes:
    "pgbench_branches_pkey" PRIMARY KEY, btree (bid)

postgres=# select count(1) from pgbench_tellers ;
 count 
-------
  1000
(1 row)

postgres=# select count(1) from pgbench_branches ;
 count 
-------
   100
(1 row)

postgres=# 

OracleDB は OCI の Autonomous AI Database (Always Free) を使用しました。
データは PostgreSQL からエクスポートした pgbench のデータをインポートしました。
filler列はどちらも特に値が入っていませんでしたので、削除しています。

オブジェクト情報/OracleDB

SQL> desc pgbench_tellers
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TID                                       NOT NULL NUMBER
 BID                                                NUMBER
 TBALANCE                                           NUMBER

SQL> desc pgbench_branches
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BID                                       NOT NULL NUMBER
 BBALANCE                                           NUMBER

SQL> select table_name,index_name from user_indexes;

TABLE_NAME
--------------------------------------------------------------------------------
INDEX_NAME
--------------------------------------------------------------------------------
PGBENCH_BRANCHES
PGBENCH_BRANCHES_PKEY

PGBENCH_TELLERS
PGBENCH_TELLERS_PKEY


SQL> 
SQL> select count(1) from pgbench_tellers;

  COUNT(1)
----------
      1000

SQL> select count(1) from pgbench_branches;

  COUNT(1)
----------
       100

SQL> 
SQL> 

実践

PostgreSQL の実行計画は下記の通りです。

実行計画/PostgreSQL

postgres=# EXPLAIN SELECT * FROM pgbench_branches b JOIN pgbench_tellers t ON t.bid = b.bid;
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Hash Join  (cost=3.25..21.99 rows=1000 width=716)
   Hash Cond: (t.bid = b.bid)
   ->  Seq Scan on pgbench_tellers t  (cost=0.00..16.00 rows=1000 width=352)
   ->  Hash  (cost=2.00..2.00 rows=100 width=364)
         ->  Seq Scan on pgbench_branches b  (cost=0.00..2.00 rows=100 width=364)
(5 rows)

postgres=#

ツリー構造になおすと下記のようになり、実行順序は

1. Seq Scan on pgbench_tellers t
2. Seq Scan on pgbench_branches b
3. Hash
4. Hash Join

となります。

OracleDB の実行計画は下記の通りです。

実行計画/OracleDB

SQL> SELECT * FROM pgbench_branches b JOIN pgbench_tellers t ON t.bid = b.bid;
(snip)

Execution Plan
----------------------------------------------------------
Plan hash value: 3713136398

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |  1000 | 14000 |     4   (0)| 00:00:01 |
|   1 |  RESULT CACHE       | ava512n11qzk7ck8xhyt2frftr |  1000 | 14000 |     4   (0)| 00:00:01 |
|*  2 |   HASH JOIN         |                            |  1000 | 14000 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| PGBENCH_BRANCHES           |   100 |   500 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| PGBENCH_TELLERS            |  1000 |  9000 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("T"."BID"="B"."BID")

Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=5; dependencies=(PGBENCH.PGBENCH_TELLERS, PGBENCH.PGBENCH_BRANCHES); name="SELECT * FROM pgbench_branches b JOIN pgbench_tellers t ON t.bid = b.bid"

Note
-----
   - SQL plan baseline "SQL_PLAN_6y1780q7xpgr19e05fb6e" used for this statement

SQL>

ツリー構造になおすと下記のようになり、実行順序は

1. TABLE ACCESS FULL (PGBENCH_BRANCHES)
2. TABLE ACCESS FULL (PGBENCH_TELLERS)
3. HASH JOIN
4. RESULT CACHE
5. SELECT STATEMENT

となります。

ぱっと見で分かる違いとして、OracleDB の実行計画には、PostgreSQL の実行計画にないオペレーション (RESULT CACHE, SELECT STATEMENT) が登場しています。

RESULT CACHE

RESULT CACHE は、SQL や PL/SQLファンクションの実行結果自体をメモリに保存し、同じクエリが繰り返し実行された際に結果を高速に返す機能です。11g から登場しました。
PostgreSQL には相当する機能が標準では存在せず、OracleDB固有の機能でありオペレーションです。

SELECT STATEMENT

SELECT STATEMENT は、OracleDB の実行計画の最上位に付くステートメント全体のルート行で、行取得は行いません。
PostgreSQLでは Hash Join や Aggregate などがルート行となり、SELECT STATEMENT に類するものは表示されません。

Seq Scan と TABLE ACCESS FULL

RESULT CACHE と SELECT STATEMENT は OracleDB固有のもので、見比べるにあたってはあまり気にする必要のないことがわかったので、残りの部分を見ていきます。
PostgreSQL では両テーブルに対して Seq Scan、OracleDB では両テーブルに対して TABLE ACCESS FULL (TAF) を行っていることがわかります。
Seq Scan と TAF のどちらも同じ意味で、テーブル全体を読みだすオペレーションとなります。

HASH

最後に、PostgreSQL の方はどちらをハッシュテーブルにしているかが明確ですが (branches)、OracleDB の方は不明確です。
津島博士のブログによると、HASH JOIN の直下でアクセスしている表がハッシュテーブルとなるようです。(PostgreSQL と同様に branches がハッシュテーブル)

以下の2つの実行計画は、3番目の表’tab1’が左側はBuild表、右側はProbe表になっています（Build表のときは’HASH JOIN’のすぐ下にきます）。

みなさんはどちらの実行計画の表示が好みでしょうか