explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fthe

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.665 1,579.719 ↑ 229.0 1 1

GroupAggregate (cost=143,299.72..143,328.34 rows=229 width=225) (actual time=1,579.719..1,579.719 rows=1 loops=1)

2. 4.417 1,579.054 ↓ 1.7 400 1

Sort (cost=143,299.72..143,300.29 rows=229 width=225) (actual time=1,579.030..1,579.054 rows=400 loops=1)

  • Sort Key: oop.name, ((((uu.firstname)::text || ' '::text) || (uu.lastname)::text)), ((((uupp.firstname)::text || ' '::text) || (uupp.lastname)::text)), ((((uusp.firstname)::text || ' '::text) || (uusp.lastname)::text)), oop.id, oop.create_date, oop.last_updated, oop.pick_location, oop.picker_uid, ((((uup.firstname)::text || ' '::text) || (uup.lastname)::text)), loc.name, oop.secondary_picker, oop.primary_picker, oop.bin_regex, oop.bin_exclude_regex, oop.it_locked_bins, oop.include_orders, oop.include_resupply, oop.include_dc, oop.include_dispatch, oop.include_spo, oop.include_vendor_returns, oop.include_new_stores, oop.resort, oop.active, oop.is_consolidate, oop.consolidate_queue
  • Sort Method: quicksort Memory: 131kB
3. 0.341 1,574.637 ↓ 1.7 400 1

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

  • Join Filter: (oop.id = oopl.one_off_pick_id)
4. 0.002 0.108 ↑ 1.0 1 1

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

5. 0.001 0.106 ↑ 1.0 1 1

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

6. 0.001 0.096 ↑ 1.0 1 1

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

7. 0.005 0.095 ↑ 1.0 1 1

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

8. 0.045 0.083 ↑ 1.0 1 1

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

  • Hash Cond: (loc.loc = oop.pick_location)
9. 0.016 0.016 ↑ 1.0 91 1

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

10. 0.003 0.022 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
11. 0.019 0.019 ↑ 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.019 rows=1 loops=1)

  • Filter: ((active = ANY ('{t,f}'::boolean[])) AND (id = 13,406))
  • Rows Removed by Filter: 44
12. 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)
13. 0.000 0.000 ↓ 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.000..0.000 rows=0 loops=1)

  • Index Cond: (user_id = oop.picker_uid)
14. 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.003..0.009 rows=1 loops=1)

  • Index Cond: (user_id = oop.primary_picker)
15. 0.000 0.000 ↓ 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.000..0.000 rows=0 loops=1)

  • Index Cond: (user_id = oop.secondary_picker)
16. 7.010 1,574.188 ↓ 1.7 400 1

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

  • Hash Cond: (one_off_pick_picks.one_off_pick_lines_id = oopl.id)
17. 65.879 1,564.419 ↑ 1.3 66,935 1

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

18. 923.747 1,498.540 ↓ 1.1 92,045 1

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

  • Hash Cond: (one_off_pick_picks.one_off_pick_lines_id = one_off_pick_lines.id)
19. 492.531 492.531 ↑ 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.005..492.531 rows=2,681,911 loops=1)

  • Filter: active
  • Rows Removed by Filter: 2,164
20. 18.959 82.262 ↑ 1.0 70,243 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 2,470kB
21. 52.891 63.303 ↑ 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.076..63.303 rows=70,243 loops=1)

  • Recheck Cond: (one_off_pick_id = 13,406)
22. 10.412 10.412 ↑ 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.412..10.412 rows=70,264 loops=1)

  • Index Cond: (one_off_pick_id = 13,406)
23. 0.126 2.759 ↓ 1.7 400 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
24. 2.633 2.633 ↓ 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.022..2.633 rows=400 loops=1)

  • Index Cond: (active = true)
  • Filter: (active AND (one_off_pick_id = 13,406))