Fight the Future

Java言語とJVM、そしてJavaエコシステム全般にまつわること

有効期間があるレコードを取得するSQLの書き方

よくある履歴管理みたいなやつ。
パフォチューしてて『アート・オブ・SQL』調べてたら載ってた。

たとえばこんなの。
# PostgreSQL 8.1で動作を確認。

CREATE TABLE item_history
             (
                          item_no   VARCHAR    ,
                          item_name VARCHAR    ,
                          price     INTEGER    ,
                          enforcement_date DATE,
                          PRIMARY KEY(item_no, enforcement_date)
             );
			 

INSERT
INTO   item_history
       (
              item_no  ,
              item_name,
              price    ,
              enforcement_date
       )
       VALUES
       (
              'A001',
              'ボール' ,
              100   ,
              '2008/01/01'
       );

INSERT
INTO   item_history
       (
              item_no  ,
              item_name,
              price    ,
              enforcement_date
       )
       VALUES
       (
              'A001',
              'ボール' ,
              200   ,
              '2008/02/01'
       );

INSERT
INTO   item_history
       (
              item_no  ,
              item_name,
              price    ,
              enforcement_date
       )
       VALUES
       (
              'A001',
              'ボール2',
              500   ,
              '2008/03/15'
       );
item_no item_name price enforcement_date
A001 ボール 100 2008/01/01
A001 ボール 200 2008/02/01
A001 ボール2 500 2008/03/15


今日時点で有効なレコードを取得する。
(あまりいないと思うけど)こういう書き方もできる。

SELECT *
FROM   item_history AS outer_table
WHERE  item_no          = 'A001'
   AND enforcement_date =
       (SELECT MAX(enforcement_date)
       FROM    item_history AS inner_table
       WHERE   inner_table.item_no           = outer_table.item_no
           AND inner_table.enforcement_date <= NOW()
       )

これは相関サブクエリであるため、比較の回数が多くなる。
実行計画はこうなる。

QUERY PLAN
Bitmap Heap Scan on item_history outer_table  (cost=2.01..32.99 rows=1 width=72) (actual time=0.243..0.245 rows=1 loops=1)
  Recheck Cond: ((item_no)::text = 'A001'::text)
  Filter: (enforcement_date = (subplan))
  ->  Bitmap Index Scan on item_history_pkey  (cost=0.00..2.01 rows=4 width=0) (actual time=0.083..0.083 rows=3 loops=1)
        Index Cond: ((item_no)::text = 'A001'::text)
  SubPlan
    ->  Result  (cost=5.83..5.84 rows=1 width=0) (actual time=0.031..0.032 rows=1 loops=3)
          InitPlan
            ->  Limit  (cost=0.00..5.83 rows=1 width=4) (actual time=0.018..0.020 rows=1 loops=3)
                  ->  Index Scan Backward using item_history_pkey on item_history inner_table  (cost=0.00..5.83 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=3)
                        Index Cond: (((item_no)::text = ($0)::text) AND (enforcement_date <= now()))
                        Filter: (enforcement_date IS NOT NULL)
Total runtime: 0.313 ms

比較するためにBitmap Heap ScanとかSubPlanとかになってるっぽい。


しかし、現実A001のレコードしか比較する必要はないため、相関ではなく非相関サブクエリで十分だ。

SELECT *
FROM   item_history AS outer_table
WHERE  item_no          = 'A001'
   AND enforcement_date =
       (SELECT MAX(enforcement_date)
       FROM    item_history AS inner_table
       WHERE   inner_table.item_no           = 'A001'
           AND inner_table.enforcement_date <= NOW()
       )

比較がなくなるので、コストも低くなる。

QUERY PLAN
Index Scan using item_history_pkey on item_history outer_table  (cost=5.84..11.67 rows=1 width=72) (actual time=0.063..0.066 rows=1 loops=1)
  Index Cond: (((item_no)::text = 'A001'::text) AND (enforcement_date = $1))
  InitPlan
    ->  Result  (cost=5.83..5.84 rows=1 width=0) (actual time=0.048..0.050 rows=1 loops=1)
          InitPlan
            ->  Limit  (cost=0.00..5.83 rows=1 width=4) (actual time=0.035..0.037 rows=1 loops=1)
                  ->  Index Scan Backward using item_history_pkey on item_history inner_table  (cost=0.00..5.83 rows=1 width=4) (actual time=0.028..0.028 rows=1 loops=1)
                        Index Cond: (((item_no)::text = 'A001'::text) AND (enforcement_date <= now()))
                        Filter: (enforcement_date IS NOT NULL)
Total runtime: 0.122 ms


主キー列を明示的にして強調する、IN句を使った方法もある。

SELECT *
FROM   item_history AS outer_table
WHERE
       (
              item_no, enforcement_date
       )
       IN
          (SELECT  item_no,
                   MAX(enforcement_date)
          FROM     item_history AS inner_table
          WHERE    inner_table.item_no           = 'A001'
               AND inner_table.enforcement_date <= NOW()
          GROUP BY inner_table.item_no
          )

これもほとんどコストは同じ。

QUERY PLAN
Nested Loop  (cost=0.00..11.70 rows=1 width=72) (actual time=0.068..0.078 rows=1 loops=1)
  ->  GroupAggregate  (cost=0.00..5.85 rows=1 width=36) (actual time=0.052..0.054 rows=1 loops=1)
        ->  Index Scan using item_history_pkey on item_history inner_table  (cost=0.00..5.83 rows=1 width=36) (actual time=0.025..0.034 rows=3 loops=1)
              Index Cond: (((item_no)::text = 'A001'::text) AND (enforcement_date <= now()))
  ->  Index Scan using item_history_pkey on item_history outer_table  (cost=0.00..5.83 rows=1 width=72) (actual time=0.004..0.007 rows=1 loops=1)
        Index Cond: (((outer_table.item_no)::text = ("outer".item_no)::text) AND (outer_table.enforcement_date = "outer"."?column2?"))
Total runtime: 0.141 ms