clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2042934 fiddles created (16669 in the last week).

CREATE TABLE good ( good_id serial PRIMARY KEY , good text NOT NULL ); CREATE TABLE inventory ( good_id int REFERENCES good (good_id) , the_date date NOT NULL , quantity int NOT NULL , PRIMARY KEY(good_id, the_date) ); CREATE TABLE price ( good_id int REFERENCES good (good_id) , the_date date NOT NULL , price numeric NOT NULL , PRIMARY KEY(good_id, the_date)); INSERT INTO good (good_id, good) SELECT g, 'good' || g AS good FROM generate_series(0,250) g; INSERT INTO inventory (good_id, the_date, quantity) SELECT DISTINCT ON (good_id, the_date) (random() * 250)::int AS good_id , '2013-01-01'::date + (random() * 365)::int AS the_date , (random()^3 * 100)::int + 1 AS quantity FROM generate_series(1,20000) g; INSERT INTO price (good_id, the_date, price) SELECT DISTINCT ON (good_id, the_date) (random() * 250)::int AS good_id , '2013-01-01'::date + (random() * 365)::int AS the_date , round((random() * 1000 + 1)::numeric, 2) AS price FROM generate_series(1,20000) g; ANALYZE good; ANALYZE inventory; ANALYZE price;
251 rows affected
17940 rows affected
17942 rows affected
 hidden batch(es)


-- DISTINCT ON EXPLAIN ANALYZE SELECT DISTINCT ON (i.the_date) i.the_date, p.the_date AS pricing_date, g.good, i.quantity, p.price FROM inventory i JOIN good g USING (good_id) LEFT JOIN price p ON p.good_id = i.good_id AND p.the_date <= i.the_date -- WHERE g.good = 'good23' ORDER BY i.the_date, p.the_date DESC;
QUERY PLAN
Unique (cost=68813.01..70934.32 rows=366 width=25) (actual time=971.682..1240.858 rows=366 loops=1)
-> Sort (cost=68813.01..69873.67 rows=424262 width=25) (actual time=971.680..1182.195 rows=647578 loops=1)
Sort Key: i.the_date, p.the_date DESC
Sort Method: external merge Disk: 23512kB
-> Nested Loop Left Join (cost=7.93..19002.48 rows=424262 width=25) (actual time=0.143..431.172 rows=647578 loops=1)
-> Hash Join (cost=7.65..331.93 rows=17940 width=19) (actual time=0.131..23.278 rows=17940 loops=1)
Hash Cond: (i.good_id = g.good_id)
-> Seq Scan on inventory i (cost=0.00..276.40 rows=17940 width=12) (actual time=0.021..3.292 rows=17940 loops=1)
-> Hash (cost=4.51..4.51 rows=251 width=11) (actual time=0.100..0.101 rows=251 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 19kB
-> Seq Scan on good g (cost=0.00..4.51 rows=251 width=11) (actual time=0.017..0.054 rows=251 loops=1)
-> Index Scan using price_pkey on price p (cost=0.29..0.80 rows=24 width=14) (actual time=0.004..0.014 rows=36 loops=17940)
Index Cond: ((good_id = i.good_id) AND (the_date <= i.the_date))
Planning Time: 1.078 ms
Execution Time: 1244.630 ms
 hidden batch(es)


-- NOT EXISTS EXPLAIN ANALYZE SELECT i.the_date, p.the_date AS pricing_date, g.good, i.quantity, p.price FROM inventory i JOIN good g USING (good_id) LEFT JOIN price p ON p.good_id = i.good_id AND p.the_date <= i.the_date -- WHERE g.good = 'good23' AND NOT EXISTS ( SELECT 1 FROM price p1 WHERE p1.good_id = p.good_id AND p1.the_date <= i.the_date AND p1.the_date > p.the_date );
QUERY PLAN
Nested Loop (cost=0.43..3629392.88 rows=212131 width=25) (actual time=0.032..3241.914 rows=17940 loops=1)
-> Nested Loop Left Join (cost=0.29..3594747.75 rows=212131 width=22) (actual time=0.027..3203.696 rows=17940 loops=1)
-> Seq Scan on inventory i (cost=0.00..276.40 rows=17940 width=12) (actual time=0.013..4.085 rows=17940 loops=1)
-> Index Scan using price_pkey on price p (cost=0.29..200.24 rows=12 width=14) (actual time=0.176..0.176 rows=1 loops=17940)
Index Cond: ((good_id = i.good_id) AND (the_date <= i.the_date))
Filter: (NOT (SubPlan 1))
Rows Removed by Filter: 35
SubPlan 1
-> Index Only Scan using price_pkey on price p1 (cost=0.29..8.31 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=647370)
Index Cond: ((good_id = p.good_id) AND (the_date <= i.the_date) AND (the_date > p.the_date))
Heap Fetches: 6538
-> Index Scan using good_pkey on good g (cost=0.14..0.16 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=17940)
Index Cond: (good_id = i.good_id)
Planning Time: 0.971 ms
Execution Time: 3243.950 ms
 hidden batch(es)