explain.depesz.com

PostgreSQL's explain analyze made readable

Result: urdG

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.013 91.077 ↑ 1.0 50 1

Limit (cost=395,160.92..395,161.04 rows=50 width=1,018) (actual time=91.045..91.077 rows=50 loops=1)

2. 0.790 91.064 ↑ 14.6 50 1

Sort (cost=395,160.92..395,162.74 rows=729 width=1,018) (actual time=91.045..91.064 rows=50 loops=1)

  • Sort Key: v.updated_at DESC
  • Sort Method: top-N heapsort Memory: 89kB
3. 16.141 90.274 ↓ 1.0 747 1

Nested Loop Left Join (cost=1,966.93..395,136.70 rows=729 width=1,018) (actual time=12.396..90.274 rows=747 loops=1)

4. 3.158 31.554 ↓ 1.0 747 1

Hash Right Join (cost=1,441.35..6,335.78 rows=729 width=962) (actual time=12.035..31.554 rows=747 loops=1)

  • Hash Cond: (d.id = va.dock_id)
5. 5.899 21.407 ↑ 1.0 25,562 1

Merge Right Join (cost=149.13..4,943.06 rows=25,562 width=470) (actual time=5.034..21.407 rows=25,562 loops=1)

  • Merge Cond: (dg.id = d.dock_group_id)
6. 0.901 8.070 ↓ 1.0 1,094 1

GroupAggregate (cost=148.84..2,981.61 rows=1,089 width=602) (actual time=5.001..8.070 rows=1,094 loops=1)

  • Group Key: dg.id
7. 0.126 7.169 ↑ 98.2 1,420 1

Nested Loop Left Join (cost=148.84..2,622.24 rows=139,392 width=466) (actual time=2.032..7.169 rows=1,420 loops=1)

8. 0.490 2.783 ↓ 1.3 1,420 1

Merge Left Join (cost=148.84..231.52 rows=1,089 width=466) (actual time=1.175..2.783 rows=1,420 loops=1)

  • Merge Cond: (dg.id = et.entity_id)
9. 0.981 1.539 ↓ 1.0 1,094 1

Sort (cost=148.55..151.28 rows=1,089 width=466) (actual time=1.145..1.539 rows=1,094 loops=1)

  • Sort Key: dg.id
  • Sort Method: quicksort Memory: 646kB
10. 0.558 0.558 ↓ 1.0 1,094 1

Seq Scan on dock_group dg (cost=0.00..93.62 rows=1,089 width=466) (actual time=0.011..0.558 rows=1,094 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 173
11. 0.754 0.754 ↓ 1.8 563 1

Index Only Scan using entity_tag_must_be_uniq_for_particular_tag_and_entity on entity_tag et (cost=0.29..549.17 rows=321 width=8) (actual time=0.024..0.754 rows=563 loops=1)

  • Index Cond: (entity_kind = 'DockGroup'::text)
  • Heap Fetches: 7
12. 0.470 4.260 ↑ 128.0 1 1,420

Materialize (cost=0.00..648.64 rows=128 width=0) (actual time=0.001..0.003 rows=1 loops=1,420)

13. 3.790 3.790 ↑ 128.0 1 1

Seq Scan on dock d_1 (cost=0.00..648.00 rows=128 width=0) (actual time=0.853..3.790 rows=1 loops=1)

  • Filter: ((deleted_at IS NULL) AND (dock_group_id = id))
  • Rows Removed by Filter: 26559
14. 7.438 7.438 ↑ 1.0 25,562 1

Index Scan using "proper dock dock_group index HACK BY BERGE DELETEME" on dock d (cost=0.29..1,655.20 rows=25,562 width=8) (actual time=0.012..7.438 rows=25,562 loops=1)

15. 0.361 6.989 ↓ 1.0 747 1

Hash (cost=1,283.11..1,283.11 rows=729 width=496) (actual time=6.989..6.989 rows=747 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 200kB
16. 1.519 6.628 ↓ 1.0 747 1

Hash Right Join (cost=634.05..1,283.11 rows=729 width=496) (actual time=3.604..6.628 rows=747 loops=1)

  • Hash Cond: (va.vehicle_id = v.id)
17. 1.521 1.521 ↓ 1.0 6,096 1

Seq Scan on vehicle_availability va (cost=0.00..624.82 rows=5,882 width=135) (actual time=0.007..1.521 rows=6,096 loops=1)

  • Filter: (deleted_at IS NULL)
18. 0.243 3.588 ↓ 1.0 747 1

Hash (cost=624.94..624.94 rows=729 width=361) (actual time=3.588..3.588 rows=747 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 110kB
19. 3.345 3.345 ↓ 1.0 747 1

Seq Scan on vehicle v (cost=0.00..624.94 rows=729 width=361) (actual time=0.014..3.345 rows=747 loops=1)

  • Filter: ((system_id)::text = 'trondheim'::text)
  • Rows Removed by Filter: 19415
20. 0.083 42.579 ↑ 1.0 1 747

Nested Loop Left Join (cost=525.59..533.32 rows=1 width=60) (actual time=0.056..0.057 rows=1 loops=747)

21. 0.560 3.735 ↑ 1.0 1 747

Nested Loop (cost=0.72..8.42 rows=1 width=24) (actual time=0.004..0.005 rows=1 loops=747)

22. 2.241 2.241 ↑ 1.0 1 747

Index Scan using trip_pkey on trip t (cost=0.43..8.11 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=747)

  • Index Cond: (id = va.trip_id)
  • Filter: ((deleted_at IS NULL) OR (deleted_at >= now()))
23. 0.934 0.934 ↑ 1.0 1 467

Index Only Scan using vehicle_pkey on vehicle v_1 (cost=0.29..0.31 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=467)

  • Index Cond: (id = t.vehicle_id)
  • Heap Fetches: 237
24. 10.274 38.761 ↑ 1.0 1 467

Aggregate (cost=524.86..524.88 rows=1 width=40) (actual time=0.082..0.083 rows=1 loops=467)

25. 20.081 28.487 ↑ 7.4 18 467

WindowAgg (cost=0.57..522.52 rows=134 width=48) (actual time=0.009..0.061 rows=18 loops=467)

26. 8.406 8.406 ↑ 7.4 18 467

Index Only Scan Backward using position_log_vehicle_position_idx on position_log_data (cost=0.57..487.01 rows=134 width=40) (actual time=0.008..0.018 rows=18 loops=467)

  • Index Cond: ((vehicle_id = t.vehicle_id) AND (generated_at >= t.started_at) AND (generated_at <= t.ended_at))
  • Heap Fetches: 8530
Planning time : 2.007 ms
Execution time : 91.306 ms