add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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
CREATE INDEX
INSERT 0 259201
QUERY PLAN
Result (cost=1.19..1.20 rows=1 width=16) (actual time=99.006..99.008 rows=1 loops=1)
  InitPlan 1 (returns $0)
    -> Limit (cost=0.42..0.59 rows=1 width=8) (actual time=98.929..98.930 rows=1 loops=1)
          -> Index Scan using pk_rttx on relation_tuple_transaction (cost=0.42..14904.25 rows=86024 width=8) (actual time=98.925..98.925 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.053..0.053 rows=1 loops=1)
                Index Cond: (id IS NOT NULL)
                Filter: ("timestamp" >= (now() - '00:00:05'::interval))
Planning Time: 0.723 ms
Execution Time: 99.084 ms
EXPLAIN
VACUUM
PREPARE
QUERY PLAN
Aggregate (cost=8.46..8.47 rows=1 width=16) (actual time=0.021..0.022 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.008 rows=2 loops=1)
        Index Cond: ("timestamp" >= '2023-03-25 11:31:24.813726'::timestamp without time zone)
Planning Time: 0.407 ms
Execution Time: 0.109 ms
EXPLAIN
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-03-25 11:31:24.814468'::timestamp without time zone)
Planning Time: 0.206 ms
Execution Time: 0.024 ms
EXPLAIN
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-03-25 11:31:24.814863'::timestamp without time zone)
Planning Time: 0.179 ms
Execution Time: 0.023 ms
EXPLAIN
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-03-25 11:31:24.815224'::timestamp without time zone)
Planning Time: 0.178 ms
Execution Time: 0.023 ms
EXPLAIN
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-03-25 11:31:24.815609'::timestamp without time zone)
Planning Time: 0.176 ms
Execution Time: 0.023 ms
EXPLAIN
QUERY PLAN
Result (cost=1.06..1.07 rows=1 width=16) (actual time=54.566..54.567 rows=1 loops=1)
  InitPlan 1 (returns $0)
    -> Limit (cost=0.42..0.53 rows=1 width=8) (actual time=54.531..54.532 rows=1 loops=1)
          -> Index Scan using pk_rttx on relation_tuple_transaction (cost=0.42..9445.44 rows=86400 width=8) (actual time=54.529..54.529 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.027..0.027 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.026..0.026 rows=1 loops=1)
                Index Cond: (id IS NOT NULL)
                Filter: ("timestamp" >= $1)
Planning Time: 0.158 ms
Execution Time: 54.589 ms
EXPLAIN