Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 251

INSERT 0 18003

INSERT 0 18014

ANALYZE

ANALYZE

ANALYZE

-- 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=68854.46..70983.48 rows=366 width=25) (actual time=645.919..863.301 rows=366 loops=1)

  ->  Sort  (cost=68854.46..69918.97 rows=425804 width=25) (actual time=645.917..813.036 rows=660207 loops=1)

        Sort Key: i.the_date, p.the_date DESC

        Sort Method: external merge  Disk: 23952kB

        ->  Hash Right Join  (cost=558.77..18853.64 rows=425804 width=25) (actual time=10.264..180.273 rows=660207 loops=1)

              Hash Cond: (p.good_id = i.good_id)

              Join Filter: (p.the_date <= i.the_date)

              Rows Removed by Join Filter: 634638

              ->  Seq Scan on price p  (cost=0.00..278.14 rows=18014 width=14) (actual time=0.016..3.196 rows=18014 loops=1)

              ->  Hash  (cost=333.74..333.74 rows=18003 width=19) (actual time=10.206..10.221 rows=18003 loops=1)

                    Buckets: 32768  Batches: 1  Memory Usage: 1163kB

                    ->  Hash Join  (cost=7.65..333.74 rows=18003 width=19) (actual time=0.112..7.052 rows=18003 loops=1)

                          Hash Cond: (i.good_id = g.good_id)

                          ->  Seq Scan on inventory i  (cost=0.00..278.03 rows=18003 width=12) (actual time=0.018..2.302 rows=18003 loops=1)

                          ->  Hash  (cost=4.51..4.51 rows=251 width=11) (actual time=0.085..0.087 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.015..0.046 rows=251 loops=1)

Planning Time: 1.186 ms

Execution Time: 866.783 ms

EXPLAIN

-- 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..3642144.40 rows=212902 width=25) (actual time=0.038..2550.790 rows=18003 loops=1)

  ->  Nested Loop Left Join  (cost=0.29..3607374.01 rows=212902 width=22) (actual time=0.033..2517.421 rows=18003 loops=1)

        ->  Seq Scan on inventory i  (cost=0.00..278.03 rows=18003 width=12) (actual time=0.013..3.415 rows=18003 loops=1)

        ->  Index Scan using price_pkey on price p  (cost=0.29..200.24 rows=12 width=14) (actual time=0.139..0.139 rows=1 loops=18003)

              Index Cond: ((good_id = i.good_id) AND (the_date <= i.the_date))

              Filter: (NOT (SubPlan 1))

              Rows Removed by Filter: 36

              SubPlan 1

                ->  Index Only Scan using price_pkey on price p1  (cost=0.29..8.31 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=660041)

                      Index Cond: ((good_id = p.good_id) AND (the_date <= i.the_date) AND (the_date > p.the_date))

                      Heap Fetches: 642204

  ->  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=18003)

        Index Cond: (good_id = i.good_id)

Planning Time: 0.572 ms

Execution Time: 2552.950 ms

EXPLAIN