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 TYPE test_enum AS ENUM ('FIRST', 'SECOND');
CREATE TABLE test (enum_col test_enum, timestamp_col timestamp,
some_id int, status int) PARTITION BY RANGE (enum_col, timestamp_col);
CREATE TABLE test_FIRST_1 PARTITION OF test FOR VALUES FROM ('FIRST',
'2022-01-01') TO ('FIRST', '2022-01-02');
CREATE TABLE test_FIRST_2 PARTITION OF test FOR VALUES FROM ('FIRST',
'2022-01-02') TO ('FIRST', '2022-01-03');

CREATE TABLE test_SECOND_1 PARTITION OF test FOR VALUES FROM ('SECOND',
'2022-01-01') TO ('SECOND', '2022-01-02');
CREATE TABLE test_SECOND_2 PARTITION OF test FOR VALUES FROM ('SECOND',
'2022-01-02') TO ('SECOND', '2022-01-03');

CREATE TYPE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

  PREPARE my_prepared_statement(text, text, text) AS
  UPDATE test SET some_id
=
5 WHERE timestamp_col >= CAST($1 AS timestamp(6)) AND timestamp_col <
CAST($2 AS timestamp(6)) AND enum_col = $3::test_enum;

PREPARE

set plan_cache_mode = 'force_custom_plan'; -- or force_custom_plan
EXPLAIN ANALYZE EXECUTE my_prepared_statement('2022-01-01 01:00:00', '2022-01-01
02:00:00', 'FIRST');

SET

QUERY PLAN

Update on test  (cost=0.00..261.00 rows=4 width=26) (actual time=0.022..0.023 rows=0 loops=1)

  Update on test_first_1 test_1

  Update on test_first_2 test_2

  Update on test_second_1 test_3

  Update on test_second_2 test_4

  ->  Seq Scan on test_first_1 test_1  (cost=0.00..65.25 rows=1 width=26) (actual time=0.006..0.006 rows=0 loops=1)

        Filter: ((timestamp_col >= ('2022-01-01 01:00:00'::cstring)::timestamp(6) without time zone) AND (timestamp_col < ('2022-01-01

02:00:00'::cstring)::timestamp(6) without time zone) AND (enum_col = ('FIRST'::cstring)::test_enum))

  ->  Seq Scan on test_first_2 test_2  (cost=0.00..65.25 rows=1 width=26) (actual time=0.003..0.003 rows=0 loops=1)

        Filter: ((timestamp_col >= ('2022-01-01 01:00:00'::cstring)::timestamp(6) without time zone) AND (timestamp_col < ('2022-01-01

02:00:00'::cstring)::timestamp(6) without time zone) AND (enum_col = ('FIRST'::cstring)::test_enum))

  ->  Seq Scan on test_second_1 test_3  (cost=0.00..65.25 rows=1 width=26) (actual time=0.008..0.008 rows=0 loops=1)

        Filter: ((timestamp_col >= ('2022-01-01 01:00:00'::cstring)::timestamp(6) without time zone) AND (timestamp_col < ('2022-01-01

02:00:00'::cstring)::timestamp(6) without time zone) AND (enum_col = ('FIRST'::cstring)::test_enum))

  ->  Seq Scan on test_second_2 test_4  (cost=0.00..65.25 rows=1 width=26) (actual time=0.003..0.003 rows=0 loops=1)

        Filter: ((timestamp_col >= ('2022-01-01 01:00:00'::cstring)::timestamp(6) without time zone) AND (timestamp_col < ('2022-01-01

02:00:00'::cstring)::timestamp(6) without time zone) AND (enum_col = ('FIRST'::cstring)::test_enum))

Planning Time: 3.472 ms

Execution Time: 0.655 ms

EXPLAIN

EXPLAIN ANALYZE UPDATE test SET some_id = 5 WHERE timestamp_col >=
CAST('2022-01-01
01:00:00' AS timestamp(6)) AND timestamp_col < CAST('2022-01-01 02:00:00'
AS
timestamp(6)) AND enum_col = 'FIRST'::test_enum;

QUERY PLAN

Update on test  (cost=0.00..39.75 rows=1 width=26) (actual time=0.006..0.006 rows=0 loops=1)

  Update on test_first_1 test_1

  ->  Seq Scan on test_first_1 test_1  (cost=0.00..39.75 rows=1 width=26) (actual time=0.005..0.005 rows=0 loops=1)

        Filter: ((timestamp_col >= '2022-01-01 01:00:00'::timestamp(6) without time zone) AND (timestamp_col < '2022-01-01 02:00:00'::timestamp(6) without time zone) AND (enum_col = 'FIRST'::test_enum))

Planning Time: 0.581 ms

Execution Time: 0.102 ms

EXPLAIN