explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hXc7

Settings
# exclusive inclusive rows x rows loops node
1. 42.706 16,834.380 ↓ 2.2 10,661 1

Sort (cost=2,131,852.96..2,131,864.89 rows=4,772 width=94) (actual time=16,833.760..16,834.380 rows=10,661 loops=1)

  • Sort Key: ((count(DISTINCT CASE WHEN (((((inv.qty_available + inv.qty_hold) + inv.qty_damaged) + inv.qty_quarantine) + inv.qty_allocated) > 0) THEN inv.containerid ELSE NULL::character varying END) = 0)) DESC, (((ceiling((COALESCE(sum((((((((inv.qty_available + inv.qty_hold) + inv.qty_damaged) + inv.qty_quarantine) + inv.qty_allocated))::double precision * (CASE u.default_ouom WHEN 1 THEN ((u.ea_width * u.ea_height) * u.ea_depth) WHEN 2 THEN (((u.ip_width * u.ip_height) * u.ip_depth))::real WHEN 3 THEN ((u.case_width * u.case_height) * u.case_depth) WHEN 4 THEN ((u.pallet_width * u.pallet_height) * u.pallet_depth) ELSE '0'::real END)::double precision) / ((((((GREATEST(u.ip_case_qty, 1))::double precision * (GREATEST(u.ea_case_qty, 1))::double precision) * '50'::double precision) * '12'::double precision) * '12'::double precision) * '12'::double precision))), '0'::double precision) * '100'::double precision)))::integer = 0)) DESC, (((ceiling((COALESCE(sum(((((((inv.qty_available + inv.qty_hold) + inv.qty_damaged) + inv.qty_quarantine))::double precision * (CASE u.default_ouom WHEN 1 THEN ((u.ea_width * u.ea_height) * u.ea_depth) WHEN 2 THEN (((u.ip_width * u.ip_height) * u.ip_depth))::real WHEN 3 THEN ((u.case_width * u.case_height) * u.case_depth) WHEN 4 THEN ((u.pallet_width * u.pallet_height) * u.pallet_depth) ELSE '0'::real END)::double precision) / ((((((GREATEST(u.ip_case_qty, 1))::double precision * (GREATEST(u.ea_case_qty, 1))::double precision) * '50'::double precision) * '12'::double precision) * '12'::double precision) * '12'::double precision))), '0'::double precision) * '100'::double precision)))::integer = 0)) DESC, s.storage_id
  • Sort Method: quicksort Memory: 1884kB
2. 751.258 16,791.674 ↓ 2.2 10,661 1

GroupAggregate (cost=1,722,877.00..2,131,561.38 rows=4,772 width=94) (actual time=15,729.310..16,791.674 rows=10,661 loops=1)

  • Group Key: s.storage_id
  • Filter: ((ceiling((COALESCE(sum(((((((inv.qty_available + inv.qty_hold) + inv.qty_damaged) + inv.qty_quarantine))::double precision * (CASE u.default_ouom WHEN 1 THEN ((u.ea_width * u.ea_height) * u.ea_depth) WHEN 2 THEN (((u.ip_width * u.ip_height) * u.ip_depth))::real WHEN 3 THEN ((u.case_width * u.case_height) * u.case_depth) WHEN 4 THEN ((u.pallet_width * u.pallet_height) * u.pallet_depth) ELSE '0'::real END)::double precision) / ((((((GREATEST(u.ip_case_qty, 1))::double precision * (GREATEST(u.ea_case_qty, 1))::double precision) * '50'::double precision) * '12'::double precision) * '12'::double precision) * '12'::double precision))), '0'::double precision) * '100'::double precision)))::integer < 50)
  • Rows Removed by Filter: 10738
3. 1,037.730 16,040.416 ↑ 3.3 290,232 1

Sort (cost=1,722,877.00..1,725,265.36 rows=955,345 width=94) (actual time=15,729.005..16,040.416 rows=290,232 loops=1)

  • Sort Key: s.storage_id
  • Sort Method: external merge Disk: 30200kB
4. 336.598 15,002.686 ↑ 3.3 290,232 1

Hash Left Join (cost=209,687.14..1,600,693.95 rows=955,345 width=94) (actual time=2,290.342..15,002.686 rows=290,232 loops=1)

  • Hash Cond: (i.uom_id = u.id)
5. 314.198 13,165.477 ↑ 3.3 290,232 1

Hash Left Join (cost=86,698.01..1,421,936.63 rows=955,345 width=41) (actual time=789.505..13,165.477 rows=290,232 loops=1)

  • Hash Cond: (COALESCE(inv.kit_id, inv.item_id) = i.id)
6. 45.915 12,071.224 ↑ 3.3 290,232 1

Nested Loop Left Join (cost=0.98..1,293,020.44 rows=955,345 width=45) (actual time=0.080..12,071.224 rows=290,232 loops=1)

7. 10.933 10.933 ↓ 4.5 21,416 1

Index Scan using dv_storage_enabled_warehouse_id_mode_putaway_rack_storage_i_idx on dv_storage s (cost=0.42..1,677.88 rows=4,798 width=12) (actual time=0.042..10.933 rows=21,416 loops=1)

  • Index Cond: ((enabled = 1) AND (warehouse_id = 5) AND (mode_putaway_rack = 1))
8. 12,014.376 12,014.376 ↑ 41.5 13 21,416

Index Scan using dv_inventory_idx5 on dv_inventory inv (cost=0.57..263.74 rows=540 width=41) (actual time=0.118..0.561 rows=13 loops=21,416)

  • Index Cond: (s.id = storage_id)
  • Filter: (((((qty_available + qty_hold) + qty_damaged) + qty_quarantine) + qty_allocated) > 0)
  • Rows Removed by Filter: 864
9. 346.227 780.055 ↓ 1.0 2,272,593 1

Hash (cost=47,200.57..47,200.57 rows=2,272,116 width=12) (actual time=780.055..780.055 rows=2,272,593 loops=1)

  • Buckets: 131072 Batches: 64 Memory Usage: 2554kB
10. 433.828 433.828 ↓ 1.0 2,272,593 1

Index Only Scan using dv_item_idx1 on dv_item i (cost=0.43..47,200.57 rows=2,272,116 width=12) (actual time=0.030..433.828 rows=2,272,593 loops=1)

  • Heap Fetches: 38773
11. 526.705 1,500.611 ↓ 1.0 2,294,572 1

Hash (cost=69,668.95..69,668.95 rows=2,294,095 width=61) (actual time=1,500.611..1,500.611 rows=2,294,572 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 2982kB
12. 973.906 973.906 ↓ 1.0 2,294,572 1

Seq Scan on dv_uom u (cost=0.00..69,668.95 rows=2,294,095 width=61) (actual time=0.010..973.906 rows=2,294,572 loops=1)

Planning time : 3.301 ms
Execution time : 16,840.239 ms