explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FXeM

Settings
# exclusive inclusive rows x rows loops node
1. 4.925 482.827 ↓ 3.5 17,670 1

Append (cost=39,577.24..39,727.08 rows=4,994 width=209) (actual time=258.043..482.827 rows=17,670 loops=1)

2.          

CTE scheduled_invoices

3. 1.831 273.364 ↑ 1.3 499 1

Nested Loop Left Join (cost=15,738.79..16,341.14 rows=660 width=124) (actual time=258.036..273.364 rows=499 loops=1)

4. 15.259 271.034 ↑ 1.3 499 1

Merge Left Join (cost=15,738.51..16,100.09 rows=660 width=147) (actual time=257.984..271.034 rows=499 loops=1)

  • Merge Cond: (((arinv.invoice)::text = ((invoice.invoice_id)::text)) AND (arinv.company = ((invoice.company)::text)))
5. 2.068 85.530 ↑ 1.3 499 1

Sort (cost=13,180.47..13,182.12 rows=660 width=144) (actual time=85.420..85.530 rows=499 loops=1)

  • Sort Key: arinv.invoice, arinv.company
  • Sort Method: quicksort Memory: 95kB
6. 0.868 83.462 ↑ 1.3 499 1

Hash Left Join (cost=1,476.16..13,149.56 rows=660 width=144) (actual time=27.702..83.462 rows=499 loops=1)

  • Hash Cond: ((arinv.ship_cat = customer_entry.ship_cat) AND (arinv.ship_date = customer_entry.date) AND ((((COALESCE((arinv.c_id)::text, ''::text) || COALESCE((arinv.c_zip)::text, ''::text)) || '_'::text) || COALESCE((arinv.c_address1)::text, ''::text)) = (customer_entry.sch_cust_id)::text))
  • Filter: ((arinv.ship_cat > 2) OR (customer_entry.load_id IS NOT NULL))
  • Rows Removed by Filter: 80
7. 0.675 55.069 ↑ 1.2 579 1

Subquery Scan on arinv (cost=0.29..11,667.83 rows=710 width=232) (actual time=0.037..55.069 rows=579 loops=1)

  • Filter: (arinv.progress >= '40'::numeric)
  • Rows Removed by Filter: 1474
8. 0.662 54.394 ↑ 1.0 2,053 1

Append (cost=0.29..11,641.21 rows=2,130 width=1,206) (actual time=0.035..54.394 rows=2,053 loops=1)

9. 0.438 0.438 ↓ 1.1 188 1

Index Scan using idx_btree_arinv_status on arinv arinv_1 (cost=0.29..52.75 rows=169 width=395) (actual time=0.027..0.438 rows=188 loops=1)

  • Index Cond: ((status)::text = 'U'::text)
10. 0.489 2.319 ↑ 1.0 982 1

Subquery Scan on *SELECT* 2_1 (cost=0.42..523.80 rows=1,025 width=442) (actual time=0.026..2.319 rows=982 loops=1)

11. 1.830 1.830 ↑ 1.0 982 1

Index Scan using arinv_status_idx on arinv arinv_2 (cost=0.42..513.55 rows=1,025 width=450) (actual time=0.025..1.830 rows=982 loops=1)

  • Index Cond: ((status)::text = 'U'::text)
12. 49.277 49.277 ↑ 1.1 883 1

Seq Scan on arinv arinv_3 (cost=0.00..10,676.59 rows=935 width=447) (actual time=0.029..49.277 rows=883 loops=1)

  • Filter: ((status)::text = 'U'::text)
  • Rows Removed by Filter: 106126
13. 0.002 1.698 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=0.00..377.02 rows=1 width=630) (actual time=1.698..1.698 rows=0 loops=1)

14. 1.696 1.696 ↓ 0.0 0 1

Seq Scan on arinv arinv_4 (cost=0.00..377.01 rows=1 width=602) (actual time=1.696..1.696 rows=0 loops=1)

  • Filter: ((status)::text = 'U'::text)
  • Rows Removed by Filter: 3441
15. 15.772 27.525 ↑ 1.0 32,577 1

Hash (cost=905.77..905.77 rows=32,577 width=41) (actual time=27.524..27.525 rows=32,577 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2689kB
16. 11.753 11.753 ↑ 1.0 32,577 1

Seq Scan on customer_entry (cost=0.00..905.77 rows=32,577 width=41) (actual time=0.016..11.753 rows=32,577 loops=1)

17. 150.902 170.245 ↑ 1.0 27,857 1

Sort (cost=2,558.04..2,627.79 rows=27,899 width=23) (actual time=165.688..170.245 rows=27,857 loops=1)

  • Sort Key: ((invoice.invoice_id)::text), ((invoice.company)::text)
  • Sort Method: quicksort Memory: 2955kB
18. 19.343 19.343 ↑ 1.0 27,899 1

Seq Scan on invoice (cost=0.00..497.99 rows=27,899 width=23) (actual time=0.021..19.343 rows=27,899 loops=1)

19. 0.499 0.499 ↑ 1.0 1 499

Index Scan using uc_id_date on load (cost=0.28..0.31 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=499)

  • Index Cond: (id = customer_entry.load_id)
20.          

CTE rushed_invoices

21. 105.344 105.476 ↑ 6.0 1 1

Seq Scan on arinv arinv_5 (cost=14.85..11,226.47 rows=6 width=21) (actual time=16.937..105.476 rows=1 loops=1)

  • Filter: (is_rushed AND (progress >= '30'::numeric) AND (NOT (hashed SubPlan 2)) AND ((status)::text = 'U'::text))
  • Rows Removed by Filter: 107008
22.          

SubPlan (forSeq Scan)

23. 0.132 0.132 ↑ 1.3 499 1

CTE Scan on scheduled_invoices scheduled_invoices_1 (cost=0.00..13.20 rows=660 width=32) (actual time=0.001..0.132 rows=499 loops=1)

24.          

CTE pick_jobs

25. 87.605 87.757 ↓ 4.0 17,170 1

Seq Scan on pick_jobs pick_jobs_1 (cost=14.99..12,009.64 rows=4,328 width=17) (actual time=3.985..87.757 rows=17,170 loops=1)

  • Filter: ((NOT (hashed SubPlan 4)) AND (NOT (hashed SubPlan 5)))
  • Rows Removed by Filter: 140
26.          

SubPlan (forSeq Scan)

27. 0.151 0.151 ↑ 1.3 499 1

CTE Scan on scheduled_invoices scheduled_invoices_2 (cost=0.00..13.20 rows=660 width=32) (actual time=0.002..0.151 rows=499 loops=1)

28. 0.001 0.001 ↑ 6.0 1 1

CTE Scan on rushed_invoices rushed_invoices_1 (cost=0.00..0.12 rows=6 width=32) (actual time=0.001..0.001 rows=1 loops=1)

29. 273.900 273.900 ↑ 1.3 499 1

CTE Scan on scheduled_invoices (cost=0.00..13.20 rows=660 width=235) (actual time=258.042..273.900 rows=499 loops=1)

30. 0.003 105.485 ↑ 6.0 1 1

Subquery Scan on *SELECT* 2 (cost=0.00..0.20 rows=6 width=209) (actual time=16.944..105.485 rows=1 loops=1)

31. 105.482 105.482 ↑ 6.0 1 1

CTE Scan on rushed_invoices (cost=0.00..0.12 rows=6 width=205) (actual time=16.942..105.482 rows=1 loops=1)

32. 98.517 98.517 ↓ 4.0 17,170 1

CTE Scan on pick_jobs (cost=0.00..86.56 rows=4,328 width=209) (actual time=3.988..98.517 rows=17,170 loops=1)

Planning time : 3.115 ms
Execution time : 487.009 ms