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?.
259201 rows affected
QUERY PLAN |
---|
Result (cost=1.19..1.20 rows=1 width=16) (actual time=97.000..97.002 rows=1 loops=1) |
InitPlan 1 (returns $0) |
-> Limit (cost=0.42..0.59 rows=1 width=8) (actual time=96.933..96.935 rows=1 loops=1) |
-> Index Scan using pk_rttx on relation_tuple_transaction (cost=0.42..14904.25 rows=86024 width=8) (actual time=96.931..96.932 rows=1 loops=1) |
Index Cond: (id IS NOT NULL) |
Filter: ("timestamp" >= (now() - '00:00:05'::interval)) |
Rows Removed by Filter: 259198 |
InitPlan 2 (returns $1) |
-> Limit (cost=0.42..0.59 rows=1 width=8) (actual time=0.055..0.055 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.054..0.054 rows=1 loops=1) |
Index Cond: (id IS NOT NULL) |
Filter: ("timestamp" >= (now() - '00:00:05'::interval)) |
Planning Time: 0.706 ms |
Execution Time: 97.051 ms |
QUERY PLAN |
---|
Aggregate (cost=8.49..8.50 rows=1 width=16) (actual time=0.030..0.031 rows=1 loops=1) |
-> Index Scan using ix_relation_tuple_transaction_by_timestamp on relation_tuple_transaction (cost=0.42..8.48 rows=3 width=8) (actual time=0.023..0.024 rows=3 loops=1) |
Index Cond: ("timestamp" >= (now() - '00:00:05'::interval)) |
Planning Time: 0.434 ms |
Execution Time: 0.105 ms |