Postgres 16

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?.
-- minimal setup
CREATE TABLE metric_event (weeknr int, data text) PARTITION BY LIST (weeknr);

CREATE TABLE metric_event_14 PARTITION OF metric_event FOR VALUES IN (14);
CREATE TABLE metric_event_15 PARTITION OF metric_event FOR VALUES IN (15);

INSERT INTO metric_event VALUES
  (14, 'foo')   
, (15, 'bar')
;

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 2

-- Partition pruning does the job!
-- but partition bound must be matched - including the data type!
-- NOTE that date_part() returns double precision and EXTRACT returns numeric!
-- So cast to int!
SELECT *
FROM   metric_event
WHERE  weeknr = EXTRACT(week FROM timestamptz '2024-04-07 02:54:31+02' AT TIME ZONE 'UTC')::int  -- note the cast!
LIMIT  10;

weeknr
data

14
foo

SELECT 1

-- note: match data type for partition pruning to work!
EXPLAIN
SELECT *
FROM   metric_event
WHERE  weeknr = 14  -- defaults to int, so it matches
LIMIT  10;

QUERY PLAN

Limit  (cost=0.00..25.88 rows=6 width=36)

  ->  Seq Scan on metric_event_14 metric_event  (cost=0.00..25.88 rows=6 width=36)

        Filter: (weeknr = 14)

EXPLAIN

-- bad plan due to type mismatch!
EXPLAIN
SELECT *
FROM   metric_event
WHERE  weeknr = numeric '14'  -- type mismatch!
LIMIT  10;

QUERY PLAN

Limit  (cost=0.00..48.47 rows=10 width=36)

  ->  Append  (cost=0.00..58.16 rows=12 width=36)

        ->  Seq Scan on metric_event_14 metric_event_1  (cost=0.00..29.05 rows=6 width=36)

              Filter: ((weeknr)::numeric = '14'::numeric)

        ->  Seq Scan on metric_event_15 metric_event_2  (cost=0.00..29.05 rows=6 width=36)

              Filter: ((weeknr)::numeric = '14'::numeric)

EXPLAIN

-- bad plan due to type mismatch!
EXPLAIN
SELECT *
FROM   metric_event
WHERE  weeknr = float '14'  -- type mismatch!
LIMIT  10;

QUERY PLAN

Limit  (cost=0.00..48.47 rows=10 width=36)

  ->  Append  (cost=0.00..58.16 rows=12 width=36)

        ->  Seq Scan on metric_event_14 metric_event_1  (cost=0.00..29.05 rows=6 width=36)

              Filter: ((weeknr)::double precision = '14'::double precision)

        ->  Seq Scan on metric_event_15 metric_event_2  (cost=0.00..29.05 rows=6 width=36)

              Filter: ((weeknr)::double precision = '14'::double precision)

EXPLAIN