explain.depesz.com

PostgreSQL's explain analyze made readable

Result: beXfE : Optimization for: plan #fthe

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.007 1,484.070 ↑ 229.0 1 1

HashAggregate (cost=143,310.78..143,318.79 rows=229 width=225) (actual time=1,484.069..1,484.070 rows=1 loops=1)

2. 0.344 1,483.063 ↓ 1.7 400 1

Nested Loop Left Join (cost=141,190.17..143,290.74 rows=229 width=225) (actual time=1,460.110..1,483.063 rows=400 loops=1)

  • Join Filter: (oop.id = oopl.one_off_pick_id)
3. 0.001 0.123 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.69..39.06 rows=1 width=212) (actual time=0.082..0.123 rows=1 loops=1)

4. 0.000 0.121 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.69..30.79 rows=1 width=199) (actual time=0.081..0.121 rows=1 loops=1)

5. 0.002 0.112 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.69..22.51 rows=1 width=186) (actual time=0.077..0.112 rows=1 loops=1)

6. 0.004 0.109 ↑ 1.0 1 1

Nested Loop (cost=1.69..14.23 rows=1 width=173) (actual time=0.075..0.109 rows=1 loops=1)

7. 0.044 0.098 ↑ 1.0 1 1

Hash Right Join (cost=1.69..5.95 rows=1 width=164) (actual time=0.065..0.098 rows=1 loops=1)

  • Hash Cond: (loc.loc = oop.pick_location)
8. 0.028 0.028 ↑ 1.0 91 1

Seq Scan on location loc (cost=0.00..3.91 rows=91 width=17) (actual time=0.012..0.028 rows=91 loops=1)

9. 0.006 0.026 ↑ 1.0 1 1

Hash (cost=1.67..1.67 rows=1 width=151) (actual time=0.026..0.026 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
10. 0.020 0.020 ↑ 1.0 1 1

Seq Scan on one_off_pick oop (cost=0.00..1.67 rows=1 width=151) (actual time=0.019..0.020 rows=1 loops=1)

  • Filter: ((active = ANY ('{t,f}'::boolean[])) AND (id = 13,406))
  • Rows Removed by Filter: 44
11. 0.007 0.007 ↑ 1.0 1 1

Index Scan using user_id_unique on ult_users uu (cost=0.00..8.27 rows=1 width=17) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: (user_id = oop.create_uid)
12. 0.001 0.001 ↓ 0.0 0 1

Index Scan using user_id_unique on ult_users uup (cost=0.00..8.27 rows=1 width=17) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (user_id = oop.picker_uid)
13. 0.009 0.009 ↑ 1.0 1 1

Index Scan using user_id_unique on ult_users uupp (cost=0.00..8.27 rows=1 width=17) (actual time=0.004..0.009 rows=1 loops=1)

  • Index Cond: (user_id = oop.primary_picker)
14. 0.001 0.001 ↓ 0.0 0 1

Index Scan using user_id_unique on ult_users uusp (cost=0.00..8.27 rows=1 width=17) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (user_id = oop.secondary_picker)
15. 6.711 1,482.596 ↓ 1.7 400 1

Hash Right Join (cost=141,188.49..143,244.23 rows=229 width=17) (actual time=1,460.008..1,482.596 rows=400 loops=1)

  • Hash Cond: (one_off_pick_picks.one_off_pick_lines_id = oopl.id)
16. 62.202 1,472.699 ↑ 1.3 66,935 1

HashAggregate (cost=140,811.31..141,676.85 rows=86,554 width=8) (actual time=1,455.880..1,472.699 rows=66,935 loops=1)

17. 844.841 1,410.497 ↓ 1.1 92,045 1

Hash Join (cost=49,602.35..140,378.54 rows=86,554 width=8) (actual time=84.662..1,410.497 rows=92,045 loops=1)

  • Hash Cond: (one_off_pick_picks.one_off_pick_lines_id = one_off_pick_lines.id)
18. 481.131 481.131 ↑ 1.0 2,681,911 1

Seq Scan on one_off_pick_picks (cost=0.00..53,026.75 rows=2,682,465 width=8) (actual time=0.004..481.131 rows=2,681,911 loops=1)

  • Filter: active
  • Rows Removed by Filter: 2,164
19. 17.794 84.525 ↑ 1.0 70,243 1

Hash (cost=48,698.21..48,698.21 rows=72,331 width=4) (actual time=84.525..84.525 rows=70,243 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 2,470kB
20. 56.440 66.731 ↑ 1.0 70,243 1

Bitmap Heap Scan on one_off_pick_lines (cost=1,357.06..48,698.21 rows=72,331 width=4) (actual time=15.015..66.731 rows=70,243 loops=1)

  • Recheck Cond: (one_off_pick_id = 13,406)
21. 10.291 10.291 ↑ 1.0 70,264 1

Bitmap Index Scan on one_off_pick_lines_one_off_pick_id_idx (cost=0.00..1,338.98 rows=72,331 width=0) (actual time=10.291..10.291 rows=70,264 loops=1)

  • Index Cond: (one_off_pick_id = 13,406)
22. 0.123 3.186 ↓ 1.7 400 1

Hash (cost=374.32..374.32 rows=229 width=17) (actual time=3.186..3.186 rows=400 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
23. 3.063 3.063 ↓ 1.7 400 1

Index Scan using one_off_pick_lines_active_seq on one_off_pick_lines oopl (cost=0.00..374.32 rows=229 width=17) (actual time=0.013..3.063 rows=400 loops=1)

  • Index Cond: (active = true)
  • Filter: (active AND (one_off_pick_id = 13,406))
  • Rows Removed by Filter: 6,586