Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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 18033

INSERT 0 17973

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=68834.64..70961.04 rows=366 width=25) (actual time=525.086..703.450 rows=366 loops=1)

  ->  Sort  (cost=68834.64..69897.84 rows=425280 width=25) (actual time=525.084..662.006 rows=643664 loops=1)

        Sort Key: i.the_date, p.the_date DESC

        Sort Method: external merge  Disk: 23352kB

        ->  Hash Right Join  (cost=559.53..18897.10 rows=425280 width=25) (actual time=8.672..152.128 rows=643664 loops=1)

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

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

              Rows Removed by Join Filter: 648092

              ->  Seq Scan on price p  (cost=0.00..277.73 rows=17973 width=14) (actual time=0.093..2.941 rows=17973 loops=1)

              ->  Hash  (cost=334.12..334.12 rows=18033 width=19) (actual time=8.544..8.558 rows=18033 loops=1)

                    Buckets: 32768  Batches: 1  Memory Usage: 1165kB

                    ->  Hash Join  (cost=7.65..334.12 rows=18033 width=19) (actual time=0.099..5.937 rows=18033 loops=1)

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

                          ->  Seq Scan on inventory i  (cost=0.00..278.33 rows=18033 width=12) (actual time=0.017..2.010 rows=18033 loops=1)

                          ->  Hash  (cost=4.51..4.51 rows=251 width=11) (actual time=0.075..0.077 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.013..0.041 rows=251 loops=1)

Planning Time: 0.899 ms

Execution Time: 706.281 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..3648111.42 rows=212640 width=25) (actual time=0.035..2044.289 rows=18033 loops=1)

  ->  Nested Loop Left Join  (cost=0.29..3613384.14 rows=212640 width=22) (actual time=0.031..2017.513 rows=18033 loops=1)

        ->  Seq Scan on inventory i  (cost=0.00..278.33 rows=18033 width=12) (actual time=0.012..3.184 rows=18033 loops=1)

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

              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.003..0.003 rows=1 loops=643473)

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

                      Heap Fetches: 625631

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

        Index Cond: (good_id = i.good_id)

Planning Time: 0.609 ms

Execution Time: 2046.228 ms

EXPLAIN