explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1ZX

Settings
# exclusive inclusive rows x rows loops node
1. 5.072 491.073 ↓ 1.9 17,670 1

Append (cost=27,285.24..40,297.61 rows=9,315 width=209) (actual time=262.531..491.073 rows=17,670 loops=1)

2.          

CTE scheduled_invoices

3. 1.854 278.521 ↑ 1.3 499 1

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

4. 15.486 276.168 ↑ 1.3 499 1

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

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

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

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

Hash Left Join (cost=1,476.16..13,149.56 rows=660 width=144) (actual time=26.892..89.434 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.783 61.856 ↑ 1.2 579 1

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

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

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

9. 0.542 0.542 ↓ 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.122..0.542 rows=188 loops=1)

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

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

11. 4.072 4.072 ↑ 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.026..4.072 rows=982 loops=1)

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

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

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

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

14. 2.768 2.768 ↓ 0.0 0 1

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

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

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

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

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

17. 148.898 169.191 ↑ 1.0 27,857 1

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

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

Seq Scan on invoice (cost=0.00..497.99 rows=27,899 width=23) (actual time=0.048..20.293 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.502 105.502 ↑ 1.1 11 1

Seq Scan on arinv arinv_5 (cost=0.00..10,944.11 rows=12 width=21) (actual time=9.138..105.502 rows=11 loops=1)

  • Filter: (is_rushed AND (progress >= '30'::numeric) AND ((status)::text = 'U'::text))
  • Rows Removed by Filter: 106998
22. 279.053 279.053 ↑ 1.3 499 1

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

23. 0.005 105.857 ↑ 6.0 1 1

Subquery Scan on *SELECT* 2 (cost=21.45..21.98 rows=6 width=209) (actual time=79.077..105.857 rows=1 loops=1)

24. 0.033 105.852 ↑ 6.0 1 1

Hash Anti Join (cost=21.45..21.91 rows=6 width=205) (actual time=79.074..105.852 rows=1 loops=1)

  • Hash Cond: ((rushed_invoices.autoid)::text = (scheduled_invoices_1.invoice_autoid)::text)
25. 105.528 105.528 ↑ 1.1 11 1

CTE Scan on rushed_invoices (cost=0.00..0.24 rows=12 width=36) (actual time=9.142..105.528 rows=11 loops=1)

26. 0.145 0.291 ↑ 1.3 499 1

Hash (cost=13.20..13.20 rows=660 width=32) (actual time=0.291..0.291 rows=499 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
27. 0.146 0.146 ↑ 1.3 499 1

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

28. 7.370 101.091 ↓ 2.0 17,170 1

Hash Anti Join (cost=21.84..12,884.10 rows=8,649 width=194) (actual time=9.911..101.091 rows=17,170 loops=1)

  • Hash Cond: ((pick_jobs.invoice)::text = (rushed_invoices_1.autoid)::text)
29. 6.649 93.711 ↓ 2.0 17,171 1

Hash Anti Join (cost=21.45..12,774.49 rows=8,655 width=17) (actual time=9.894..93.711 rows=17,171 loops=1)

  • Hash Cond: ((pick_jobs.invoice)::text = (scheduled_invoices_2.invoice_autoid)::text)
30. 86.748 86.748 ↑ 1.0 17,310 1

Seq Scan on pick_jobs (cost=0.00..11,908.10 rows=17,310 width=17) (actual time=9.560..86.748 rows=17,310 loops=1)

31. 0.166 0.314 ↑ 1.3 499 1

Hash (cost=13.20..13.20 rows=660 width=32) (actual time=0.314..0.314 rows=499 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
32. 0.148 0.148 ↑ 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.148 rows=499 loops=1)

33. 0.005 0.010 ↑ 1.1 11 1

Hash (cost=0.24..0.24 rows=12 width=32) (actual time=0.010..0.010 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.005 0.005 ↑ 1.1 11 1

CTE Scan on rushed_invoices rushed_invoices_1 (cost=0.00..0.24 rows=12 width=32) (actual time=0.001..0.005 rows=11 loops=1)

Planning time : 3.099 ms
Execution time : 494.825 ms