よくある履歴管理みたいなやつ。
パフォチューしてて『アート・オブ・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