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
INSERT 0 100000
ALTER TABLE
CREATE INDEX
SELECT 57
ANALYZE
ANALYZE
CREATE FUNCTION
QUERY PLAN |
---|
Unique (cost=10040.82..10540.82 rows=57 width=12) (actual time=89.141..105.614 rows=57 loops=1) |
-> Sort (cost=10040.82..10290.82 rows=100000 width=12) (actual time=89.138..99.118 rows=100000 loops=1) |
Sort Key: equipment_id, created_at DESC |
Sort Method: external merge Disk: 2552kB |
-> Seq Scan on geoposition_records (cost=0.00..1736.00 rows=100000 width=12) (actual time=0.018..20.364 rows=100000 loops=1) |
Planning Time: 0.265 ms |
Execution Time: 106.055 ms |
EXPLAIN
QUERY PLAN |
---|
HashAggregate (cost=2236.00..2236.57 rows=57 width=12) (actual time=28.620..28.640 rows=57 loops=1) |
Group Key: equipment_id |
Batches: 1 Memory Usage: 32kB |
-> Seq Scan on geoposition_records (cost=0.00..1736.00 rows=100000 width=12) (actual time=0.013..8.852 rows=100000 loops=1) |
Planning Time: 0.120 ms |
Execution Time: 28.692 ms |
EXPLAIN
QUERY PLAN |
---|
Function Scan on f_latest_equip (cost=0.25..10.25 rows=1000 width=12) (actual time=2.411..2.415 rows=57 loops=1) |
Planning Time: 0.031 ms |
Execution Time: 2.470 ms |
EXPLAIN
QUERY PLAN |
---|
Seq Scan on equipment eq (cost=0.00..123.65 rows=57 width=12) (actual time=0.027..0.444 rows=57 loops=1) |
SubPlan 2 |
-> Result (cost=2.13..2.14 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=57) |
InitPlan 1 (returns $1) |
-> Limit (cost=0.42..2.13 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=57) |
-> Index Only Scan using geo_rec_mult_idx on geoposition_records (cost=0.42..3007.25 rows=1754 width=8) (actual time=0.006..0.006 rows=1 loops=57) |
Index Cond: ((equipment_id = eq.equipment_id) AND (created_at IS NOT NULL)) |
Heap Fetches: 57 |
Planning Time: 0.131 ms |
Execution Time: 0.465 ms |
EXPLAIN
QUERY PLAN |
---|
Seq Scan on equipment eq (cost=0.00..122.94 rows=57 width=12) (actual time=0.022..0.380 rows=57 loops=1) |
SubPlan 1 |
-> Limit (cost=0.42..2.13 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=57) |
-> Index Only Scan using geo_rec_mult_idx on geoposition_records (cost=0.42..3002.87 rows=1754 width=8) (actual time=0.006..0.006 rows=1 loops=57) |
Index Cond: (equipment_id = eq.equipment_id) |
Heap Fetches: 57 |
Planning Time: 0.067 ms |
Execution Time: 0.399 ms |
EXPLAIN
QUERY PLAN |
---|
Nested Loop Left Join (cost=0.42..124.08 rows=57 width=12) (actual time=0.023..0.400 rows=57 loops=1) |
-> Seq Scan on equipment eq (cost=0.00..1.57 rows=57 width=4) (actual time=0.008..0.014 rows=57 loops=1) |
-> Limit (cost=0.42..2.13 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=57) |
-> Index Only Scan using geo_rec_mult_idx on geoposition_records (cost=0.42..3002.87 rows=1754 width=8) (actual time=0.006..0.006 rows=1 loops=57) |
Index Cond: (equipment_id = eq.equipment_id) |
Heap Fetches: 57 |
Planning Time: 0.107 ms |
Execution Time: 0.420 ms |
EXPLAIN