explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ritj

Settings
# exclusive inclusive rows x rows loops node
1. 5.028 480.729 ↓ 1.9 17,670 1

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

2.          

CTE scheduled_invoices

3. 1.833 273.482 ↑ 1.3 499 1

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

4. 15.346 271.150 ↑ 1.3 499 1

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

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

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

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

Hash Left Join (cost=1,476.16..13,149.56 rows=660 width=144) (actual time=27.398..86.319 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.720 58.197 ↑ 1.2 579 1

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

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

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

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

  • Index Cond: ((status)::text = 'U'::text)
10. 0.506 2.446 ↑ 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.446 rows=982 loops=1)

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

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

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

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

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

14. 1.691 1.691 ↓ 0.0 0 1

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

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

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

  • Buckets: 32768 Batches: 1 Memory Usage: 2689kB
16. 11.947 11.947 ↑ 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.947 rows=32,577 loops=1)

17. 148.269 167.478 ↑ 1.0 27,857 1

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

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

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

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

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

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

23. 0.004 104.967 ↑ 6.0 1 1

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

24. 0.027 104.963 ↑ 6.0 1 1

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

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

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

26. 0.147 0.287 ↑ 1.3 499 1

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

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

28. 7.322 96.700 ↓ 2.0 17,170 1

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

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

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

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

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

31. 0.141 0.282 ↑ 1.3 499 1

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

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

33. 0.005 0.009 ↑ 1.1 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.004 0.004 ↑ 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.004 rows=11 loops=1)

Planning time : 2.920 ms
Execution time : 484.613 ms