explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lVJe : Optimization for: plan #UPQW

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.740 1,799.333 ↑ 229.0 1 1

GroupAggregate (cost=143,299.20..143,327.83 rows=229 width=225) (actual time=1,799.332..1,799.333 rows=1 loops=1)

2. 5.120 1,798.593 ↓ 1.7 400 1

Sort (cost=143,299.20..143,299.77 rows=229 width=225) (actual time=1,798.565..1,798.593 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.325 1,793.473 ↓ 1.7 400 1

Nested Loop Left Join (cost=141,189.68..143,290.23 rows=229 width=225) (actual time=1,768.803..1,793.473 rows=400 loops=1)

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

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

5. 0.002 0.110 ↑ 1.0 1 1

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

6. 0.001 0.098 ↑ 1.0 1 1

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

7. 0.005 0.097 ↑ 1.0 1 1

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

8. 0.041 0.085 ↑ 1.0 1 1

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

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

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

10. 0.003 0.024 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
11. 0.021 0.021 ↑ 1.0 1 1

Seq Scan on one_off_pick oop (cost=0.00..1.67 rows=1 width=151) (actual time=0.020..0.021 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.010 0.010 ↑ 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.010 rows=1 loops=1)

  • Index Cond: (user_id = oop.primary_picker)
15. 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)
16. 7.023 1,793.036 ↓ 1.7 400 1

Hash Right Join (cost=141,188.00..143,243.72 rows=229 width=17) (actual time=1,768.714..1,793.036 rows=400 loops=1)

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

HashAggregate (cost=140,810.82..141,676.35 rows=86,553 width=8) (actual time=1,765.154..1,783.407 rows=66,935 loops=1)

18. 1,061.643 1,712.727 ↓ 1.1 92,045 1

Hash Join (cost=49,602.35..140,378.05 rows=86,553 width=8) (actual time=74.587..1,712.727 rows=92,045 loops=1)

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

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

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

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

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

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

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

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

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

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