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=242.955..242.957 rows=1 loops=1) |
InitPlan 1 (returns $0) |
-> Limit (cost=0.42..0.59 rows=1 width=8) (actual time=135.186..135.187 rows=0 loops=1) |
-> Index Scan using pk_rttx on relation_tuple_transaction (cost=0.42..14904.25 rows=86024 width=8) (actual time=135.182..135.183 rows=0 loops=1) |
Index Cond: (id IS NOT NULL) |
Filter: ("timestamp" >= (now() - '00:00:05'::interval)) |
Rows Removed by Filter: 259201 |
InitPlan 2 (returns $1) |
-> Limit (cost=0.42..0.59 rows=1 width=8) (actual time=107.763..107.763 rows=0 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=107.760..107.760 rows=0 loops=1) |
Index Cond: (id IS NOT NULL) |
Filter: ("timestamp" >= (now() - '00:00:05'::interval)) |
Rows Removed by Filter: 259201 |
Planning Time: 0.987 ms |
Execution Time: 243.185 ms |
EXPLAIN
VACUUM
PREPARE
QUERY PLAN |
---|
Aggregate (cost=4.44..4.45 rows=1 width=16) (actual time=0.015..0.017 rows=1 loops=1) |
-> 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.007..0.007 rows=0 loops=1) |
Index Cond: ("timestamp" >= '2024-05-28 19:42:51.333257'::timestamp without time zone) |
Planning Time: 0.792 ms |
Execution Time: 0.122 ms |
EXPLAIN
QUERY PLAN |
---|
Aggregate (cost=4.44..4.45 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=1) |
-> 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-05-28 19:42:51.334463'::timestamp without time zone) |
Planning Time: 0.237 ms |
Execution Time: 0.027 ms |
EXPLAIN
QUERY PLAN |
---|
Aggregate (cost=4.44..4.45 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=1) |
-> 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-05-28 19:42:51.334952'::timestamp without time zone) |
Planning Time: 0.231 ms |
Execution Time: 0.025 ms |
EXPLAIN
QUERY PLAN |
---|
Aggregate (cost=4.44..4.45 rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=1) |
-> 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-05-28 19:42:51.335408'::timestamp without time zone) |
Planning Time: 0.227 ms |
Execution Time: 0.024 ms |
EXPLAIN
QUERY PLAN |
---|
Aggregate (cost=4.44..4.45 rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=1) |
-> 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-05-28 19:42:51.335877'::timestamp without time zone) |
Planning Time: 0.226 ms |
Execution Time: 0.024 ms |
EXPLAIN
QUERY PLAN |
---|
Result (cost=1.06..1.07 rows=1 width=16) (actual time=124.317..124.319 rows=1 loops=1) |
InitPlan 1 (returns $0) |
-> Limit (cost=0.42..0.53 rows=1 width=8) (actual time=67.754..67.755 rows=0 loops=1) |
-> Index Scan using pk_rttx on relation_tuple_transaction (cost=0.42..9445.44 rows=86400 width=8) (actual time=67.753..67.753 rows=0 loops=1) |
Index Cond: (id IS NOT NULL) |
Filter: ("timestamp" >= $1) |
Rows Removed by Filter: 259201 |
InitPlan 2 (returns $1) |
-> Limit (cost=0.42..0.53 rows=1 width=8) (actual time=56.558..56.558 rows=0 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=56.555..56.556 rows=0 loops=1) |
Index Cond: (id IS NOT NULL) |
Filter: ("timestamp" >= $1) |
Rows Removed by Filter: 259201 |
Planning Time: 0.200 ms |
Execution Time: 124.346 ms |
EXPLAIN