Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();

version

PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit

SELECT 1

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(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

vacuum analyze;

VACUUM

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

PREPARE

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

QUERY PLAN

Aggregate  (cost=4.44..4.45 rows=1 width=16) (actual time=0.162..0.164 rows=1 loops=1)

  Buffers: shared hit=3

  ->  Index Scan using ix_relation_tuple_transaction_by_timestamp on relation_tuple_transaction  (cost=0.42..4.44 rows=1 width=8) (actual time=0.153..0.153 rows=0 loops=1)

        Index Cond: ("timestamp" >= '2024-08-23 12:30:12.476897'::timestamp without time zone)

        Buffers: shared hit=3

Planning:

  Buffers: shared hit=69 read=4 written=4

Planning Time: 1.156 ms

Execution Time: 0.639 ms

EXPLAIN

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

QUERY PLAN

Aggregate  (cost=4.44..4.45 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=1)

  Buffers: shared hit=3

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

        Index Cond: ("timestamp" >= '2024-08-23 12:30:12.483179'::timestamp without time zone)

        Buffers: shared hit=3

Planning:

  Buffers: shared hit=12

Planning Time: 0.246 ms

Execution Time: 0.026 ms

EXPLAIN

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

QUERY PLAN

Aggregate  (cost=4.44..4.45 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=1)

  Buffers: shared hit=3

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

        Index Cond: ("timestamp" >= '2024-08-23 12:30:12.483697'::timestamp without time zone)

        Buffers: shared hit=3

Planning:

  Buffers: shared hit=12

Planning Time: 0.235 ms

Execution Time: 0.025 ms

EXPLAIN

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

QUERY PLAN

Aggregate  (cost=4.44..4.45 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=1)

  Buffers: shared hit=3

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

        Index Cond: ("timestamp" >= '2024-08-23 12:30:12.484325'::timestamp without time zone)

        Buffers: shared hit=3

Planning:

  Buffers: shared hit=12

Planning Time: 0.250 ms

Execution Time: 0.028 ms

EXPLAIN

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

QUERY PLAN

Aggregate  (cost=4.44..4.45 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=1)

  Buffers: shared hit=3

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

        Index Cond: ("timestamp" >= '2024-08-23 12:30:12.484978'::timestamp without time zone)

        Buffers: shared hit=3

Planning:

  Buffers: shared hit=12

Planning Time: 0.228 ms

Execution Time: 0.025 ms

EXPLAIN

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

QUERY PLAN

Result  (cost=1.06..1.07 rows=1 width=16) (actual time=148.783..148.785 rows=1 loops=1)

  Buffers: shared hit=1731 read=3204 written=762

  InitPlan 1 (returns $0)

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

          Buffers: shared hit=1 read=2112 written=762

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

                Index Cond: (id IS NOT NULL)

                Filter: ("timestamp" >= $1)

                Rows Removed by Filter: 259201

                Buffers: shared hit=1 read=2112 written=762

  InitPlan 2 (returns $1)

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

          Buffers: shared hit=1730 read=1092

          ->  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=61.293..61.294 rows=0 loops=1)

                Index Cond: (id IS NOT NULL)

                Filter: ("timestamp" >= $1)

                Rows Removed by Filter: 259201

                Buffers: shared hit=1730 read=1092

Planning Time: 0.228 ms

Execution Time: 149.001 ms

EXPLAIN

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

QUERY PLAN

Result  (cost=1.06..1.07 rows=1 width=16) (actual time=133.184..133.186 rows=1 loops=1)

  Buffers: shared hit=2574 read=2361

  InitPlan 1 (returns $0)

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

          Buffers: shared hit=891 read=1222

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

                Index Cond: (id IS NOT NULL)

                Filter: ("timestamp" >= $1)

                Rows Removed by Filter: 259201

                Buffers: shared hit=891 read=1222

  InitPlan 2 (returns $1)

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

          Buffers: shared hit=1683 read=1139

          ->  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=67.757..67.757 rows=0 loops=1)

                Index Cond: (id IS NOT NULL)

                Filter: ("timestamp" >= $1)

                Rows Removed by Filter: 259201

                Buffers: shared hit=1683 read=1139

Planning Time: 0.074 ms

Execution Time: 133.222 ms

EXPLAIN

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

QUERY PLAN

Result  (cost=1.06..1.07 rows=1 width=16) (actual time=149.586..149.588 rows=1 loops=1)

  Buffers: shared hit=2601 read=2334

  InitPlan 1 (returns $0)

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

          Buffers: shared hit=939 read=1174

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

                Index Cond: (id IS NOT NULL)

                Filter: ("timestamp" >= $1)

                Rows Removed by Filter: 259201

                Buffers: shared hit=939 read=1174

  InitPlan 2 (returns $1)

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

          Buffers: shared hit=1662 read=1160

          ->  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=80.551..80.551 rows=0 loops=1)

                Index Cond: (id IS NOT NULL)

                Filter: ("timestamp" >= $1)

                Rows Removed by Filter: 259201

                Buffers: shared hit=1662 read=1160

Planning Time: 0.051 ms

Execution Time: 149.623 ms

EXPLAIN