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