Postgres 14

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 one(
  one_id   int PRIMARY KEY
, cut_time timestamp
, f1       int
);

INSERT INTO one (one_id, cut_time, f1)
SELECT i, '2012-1-1'::date + interval '2 years' * random(), i	-- random timestamps for 2012 and 2013
FROM   generate_series (1, 3394) g(i); -- actual data is probably partly in order and faster to query

CREATE TABLE two(
  two_id int PRIMARY KEY
, one_id int REFERENCES one(one_id)
, f2     int
);

INSERT INTO two(two_id, one_id, f2)
SELECT i, (random() * 3393)::int + 1, i
FROM   generate_series (1, 40000) g(i);  -- 40.000 instead of 4.000.000 rows, enough to demonstrate

ANALYZE one;
ANALYZE two;

CREATE INDEX two_one_id on two(one_id);
CREATE INDEX one_cut_time on one(cut_time);

CREATE TABLE

INSERT 0 3394

CREATE TABLE

INSERT 0 40000

ANALYZE

ANALYZE

CREATE INDEX

CREATE INDEX

EXPLAIN ANALYZE
SELECT o.one_id, o.cut_time::date, o.f1, t.f2 
FROM   one o
JOIN   two t USING (one_id)
WHERE  o.cut_time >= '2013-01-01'
AND    o.cut_time <  '2013-02-01';

QUERY PLAN

Hash Join  (cost=32.28..759.10 rows=1874 width=16) (actual time=0.794..41.169 rows=1803 loops=1)

  Hash Cond: (t.one_id = o.one_id)

  ->  Seq Scan on two t  (cost=0.00..617.00 rows=40000 width=8) (actual time=0.031..24.041 rows=40000 loops=1)

  ->  Hash  (cost=30.29..30.29 rows=159 width=16) (actual time=0.723..0.727 rows=157 loops=1)

        Buckets: 1024  Batches: 1  Memory Usage: 16kB

        ->  Bitmap Heap Scan on one o  (cost=5.91..30.29 rows=159 width=16) (actual time=0.568..0.674 rows=157 loops=1)

              Recheck Cond: ((cut_time >= '2013-01-01 00:00:00'::timestamp without time zone) AND (cut_time < '2013-02-01 00:00:00'::timestamp without time zone))

              Heap Blocks: exact=22

              ->  Bitmap Index Scan on one_cut_time  (cost=0.00..5.87 rows=159 width=0) (actual time=0.529..0.529 rows=157 loops=1)

                    Index Cond: ((cut_time >= '2013-01-01 00:00:00'::timestamp without time zone) AND (cut_time < '2013-02-01 00:00:00'::timestamp without time zone))

Planning Time: 2.109 ms

Execution Time: 41.826 ms

EXPLAIN

-- disabling some planner options
SET enable_seqscan = off;
SET enable_indexscan = off;
SET enable_bitmapscan = off;

-- Now you get those insane numbers with EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT o.one_id, o.cut_time::date, o.f1, t.f2 
FROM   one o
JOIN   two t USING (one_id)
WHERE  o.cut_time >= '2013-01-01'
AND    o.cut_time <  '2013-02-01';

SET

SET

SET

QUERY PLAN

Hash Join  (cost=20000000143.44..20000001905.25 rows=1874 width=16) (actual time=1.433..43.312 rows=1803 loops=1)

  Hash Cond: (t.one_id = o.one_id)

  ->  Index Scan using two_one_id on two t  (cost=10000000000.29..10000001652.29 rows=40000 width=8) (actual time=0.019..32.601 rows=40000 loops=1)

  ->  Hash  (cost=10000000141.16..10000000141.16 rows=159 width=16) (actual time=1.401..1.403 rows=157 loops=1)

        Buckets: 1024  Batches: 1  Memory Usage: 16kB

        ->  Index Scan using one_pkey on one o  (cost=10000000000.28..10000000141.16 rows=159 width=16) (actual time=0.015..1.358 rows=157 loops=1)

              Filter: ((cut_time >= '2013-01-01 00:00:00'::timestamp without time zone) AND (cut_time < '2013-02-01 00:00:00'::timestamp without time zone))

              Rows Removed by Filter: 3237

Planning Time: 0.489 ms

Execution Time: 43.613 ms

EXPLAIN