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 relation_tuple_transaction (
    id BIGSERIAL NOT NULL UNIQUE,
    timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL UNIQUE,
    CONSTRAINT pk_rttx PRIMARY KEY (id)
);
-- CREATE INDEX ix_relation_tuple_transaction_by_timestamp on relation_tuple_transaction using brin(id, timestamp);
CREATE INDEX ix_relation_tuple_transaction_by_timestamp on relation_tuple_transaction(timestamp);

CREATE TABLE

CREATE INDEX

INSERT INTO relation_tuple_transaction(timestamp) SELECT * FROM generate_series
        ( now() - interval '3 days'
        , now()
        , '1 second'::interval) dd
        ;

INSERT 0 259201

EXPLAIN ANALYZE
SELECT MIN(id), MAX(id) from relation_tuple_transaction WHERE timestamp >= now() - interval '5 seconds';

QUERY PLAN

Result  (cost=1.19..1.20 rows=1 width=16) (actual time=101.188..101.191 rows=1 loops=1)

  InitPlan 1 (returns $0)

    ->  Limit  (cost=0.42..0.59 rows=1 width=8) (actual time=101.108..101.109 rows=1 loops=1)

          ->  Index Scan using pk_rttx on relation_tuple_transaction  (cost=0.42..14904.25 rows=86024 width=8) (actual time=101.103..101.103 rows=1 loops=1)

                Index Cond: (id IS NOT NULL)

                Filter: ("timestamp" >= (now() - '00:00:05'::interval))

                Rows Removed by Filter: 259199

  InitPlan 2 (returns $1)

    ->  Limit  (cost=0.42..0.59 rows=1 width=8) (actual time=0.054..0.054 rows=1 loops=1)

          ->  Index Scan Backward using pk_rttx on relation_tuple_transaction relation_tuple_transaction_1  (cost=0.42..14904.25 rows=86024 width=8) (actual time=0.052..0.052 rows=1 loops=1)

                Index Cond: (id IS NOT NULL)

                Filter: ("timestamp" >= (now() - '00:00:05'::interval))

Planning Time: 0.800 ms

Execution Time: 101.315 ms

EXPLAIN

vacuum analyze;

VACUUM

PREPARE ts_range (TIMESTAMP) AS SELECT MIN(id), MAX(id) from relation_tuple_transaction WHERE timestamp >= $1;

PREPARE

EXPLAIN ANALYZE
EXECUTE ts_range(now() - interval '5 seconds');

QUERY PLAN

Aggregate  (cost=8.46..8.47 rows=1 width=16) (actual time=0.029..0.030 rows=1 loops=1)

  ->  Index Scan using ix_relation_tuple_transaction_by_timestamp on relation_tuple_transaction  (cost=0.42..8.46 rows=2 width=8) (actual time=0.007..0.008 rows=2 loops=1)

        Index Cond: ("timestamp" >= '2023-10-23 11:04:30.989003'::timestamp without time zone)

Planning Time: 0.446 ms

Execution Time: 0.118 ms

EXPLAIN

EXPLAIN ANALYZE
EXECUTE ts_range(now() - interval '5 seconds');

QUERY PLAN

Aggregate  (cost=8.46..8.47 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=1)

  ->  Index Scan using ix_relation_tuple_transaction_by_timestamp on relation_tuple_transaction  (cost=0.42..8.46 rows=2 width=8) (actual time=0.006..0.007 rows=2 loops=1)

        Index Cond: ("timestamp" >= '2023-10-23 11:04:30.989824'::timestamp without time zone)

Planning Time: 0.206 ms

Execution Time: 0.026 ms

EXPLAIN

EXPLAIN ANALYZE
EXECUTE ts_range(now() - interval '5 seconds');

QUERY PLAN

Aggregate  (cost=8.46..8.47 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1)

  ->  Index Scan using ix_relation_tuple_transaction_by_timestamp on relation_tuple_transaction  (cost=0.42..8.46 rows=2 width=8) (actual time=0.005..0.006 rows=2 loops=1)

        Index Cond: ("timestamp" >= '2023-10-23 11:04:30.990248'::timestamp without time zone)

Planning Time: 0.184 ms

Execution Time: 0.024 ms

EXPLAIN

EXPLAIN ANALYZE
EXECUTE ts_range(now() - interval '5 seconds');

QUERY PLAN

Aggregate  (cost=8.46..8.47 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1)

  ->  Index Scan using ix_relation_tuple_transaction_by_timestamp on relation_tuple_transaction  (cost=0.42..8.46 rows=2 width=8) (actual time=0.005..0.006 rows=2 loops=1)

        Index Cond: ("timestamp" >= '2023-10-23 11:04:30.990618'::timestamp without time zone)

Planning Time: 0.204 ms

Execution Time: 0.024 ms

EXPLAIN

EXPLAIN ANALYZE
EXECUTE ts_range(now() - interval '5 seconds');

QUERY PLAN

Aggregate  (cost=8.46..8.47 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1)

  ->  Index Scan using ix_relation_tuple_transaction_by_timestamp on relation_tuple_transaction  (cost=0.42..8.46 rows=2 width=8) (actual time=0.005..0.006 rows=2 loops=1)

        Index Cond: ("timestamp" >= '2023-10-23 11:04:30.991008'::timestamp without time zone)

Planning Time: 0.180 ms

Execution Time: 0.023 ms

EXPLAIN

EXPLAIN ANALYZE
EXECUTE ts_range(now() - interval '5 seconds');

QUERY PLAN

Result  (cost=1.06..1.07 rows=1 width=16) (actual time=55.388..55.390 rows=1 loops=1)

  InitPlan 1 (returns $0)

    ->  Limit  (cost=0.42..0.53 rows=1 width=8) (actual time=55.348..55.349 rows=1 loops=1)

          ->  Index Scan using pk_rttx on relation_tuple_transaction  (cost=0.42..9445.44 rows=86400 width=8) (actual time=55.346..55.346 rows=1 loops=1)

                Index Cond: (id IS NOT NULL)

                Filter: ("timestamp" >= $1)

                Rows Removed by Filter: 259199

  InitPlan 2 (returns $1)

    ->  Limit  (cost=0.42..0.53 rows=1 width=8) (actual time=0.033..0.033 rows=1 loops=1)

          ->  Index Scan Backward using pk_rttx on relation_tuple_transaction relation_tuple_transaction_1  (cost=0.42..9445.44 rows=86400 width=8) (actual time=0.031..0.031 rows=1 loops=1)

                Index Cond: (id IS NOT NULL)

                Filter: ("timestamp" >= $1)

Planning Time: 0.165 ms

Execution Time: 55.412 ms

EXPLAIN