explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7VCR

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 9,366.200 ↑ 1.0 1 1

Aggregate (cost=21,270.16..21,270.17 rows=1 width=40) (actual time=9,366.200..9,366.200 rows=1 loops=1)

2. 0.023 9,366.180 ↑ 4.7 45 1

Hash Join (cost=21,174.13..21,269.10 rows=213 width=48) (actual time=9,365.903..9,366.180 rows=45 loops=1)

  • Hash Cond: (oi.c_doctype_id = dt.c_doctype_id)
3. 0.062 9,366.130 ↑ 9.7 139 1

Subquery Scan on oi (cost=21,161.80..21,253.15 rows=1,353 width=64) (actual time=9,365.872..9,366.130 rows=139 loops=1)

  • Filter: (oi.daysdue > 0)
  • Rows Removed by Filter: 1185
4. 1.614 9,366.068 ↑ 3.1 1,324 1

HashAggregate (cost=21,161.80..21,202.40 rows=4,060 width=96) (actual time=9,365.869..9,366.068 rows=1,324 loops=1)

  • Group Key: i.c_invoice_id, bp.c_bpartner_id, (paymenttermduedays(i.c_paymentterm_id, (i.dateinvoiced)::timestamp with time zone, getdate())), (invoiceopen(i.c_invoice_id, '0'::numeric)), i.c_doctype_id, i.docstatus
5. 0.250 9,364.454 ↑ 3.1 1,324 1

Append (cost=52.35..21,100.90 rows=4,060 width=96) (actual time=24.082..9,364.454 rows=1,324 loops=1)

6. 3.907 33.318 ↑ 64.8 13 1

Nested Loop (cost=52.35..6,015.36 rows=842 width=57) (actual time=24.082..33.318 rows=13 loops=1)

7. 0.006 0.006 ↑ 1.0 1 1

Index Only Scan using c_bpartner_pkey on c_bpartner bp (cost=0.29..1.41 rows=1 width=6) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (c_bpartner_id = '5005922'::numeric)
  • Heap Fetches: 0
8. 0.007 29.405 ↑ 64.8 13 1

Nested Loop Left Join (cost=52.06..5,371.93 rows=842 width=35) (actual time=23.748..29.405 rows=13 loops=1)

9. 0.004 29.372 ↑ 64.8 13 1

Nested Loop (cost=51.77..4,923.09 rows=842 width=35) (actual time=23.744..29.372 rows=13 loops=1)

10. 0.016 29.342 ↑ 64.8 13 1

Nested Loop (cost=51.49..4,663.63 rows=842 width=41) (actual time=23.739..29.342 rows=13 loops=1)

11. 0.004 29.300 ↑ 64.8 13 1

Nested Loop (cost=51.20..3,805.12 rows=842 width=41) (actual time=23.733..29.300 rows=13 loops=1)

12. 0.002 0.002 ↑ 1.0 1 1

Index Only Scan using c_bpartner_pkey on c_bpartner b (cost=0.29..1.41 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: (c_bpartner_id = '5005922'::numeric)
  • Heap Fetches: 0
13. 0.017 29.294 ↑ 64.8 13 1

Hash Join (cost=50.91..3,795.30 rows=842 width=41) (actual time=23.730..29.294 rows=13 loops=1)

  • Hash Cond: (i.c_doctype_id = d.c_doctype_id)
14. 28.991 29.235 ↑ 64.8 13 1

Bitmap Heap Scan on c_invoice i (cost=42.79..3,784.92 rows=842 width=53) (actual time=23.684..29.235 rows=13 loops=1)

  • Recheck Cond: (c_bpartner_id = '5005922'::numeric)
  • Filter: ((ispayschedulevalid <> 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL}'::bpchar[])) AND (docstatus = 'CO'::bpchar) AND (invoiceopen(c_invoice_id, '0'::numeric) <> '0'::numeric))
  • Rows Removed by Filter: 3039
  • Heap Blocks: exact=1497
15. 0.244 0.244 ↑ 1.0 3,052 1

Bitmap Index Scan on c_invoice_documentno_target (cost=0.00..42.58 rows=3,128 width=0) (actual time=0.244..0.244 rows=3,052 loops=1)

  • Index Cond: (c_bpartner_id = '5005922'::numeric)
16. 0.024 0.042 ↑ 1.0 210 1

Hash (cost=5.49..5.49 rows=210 width=6) (actual time=0.042..0.042 rows=210 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
17. 0.018 0.018 ↑ 1.0 210 1

Index Only Scan using c_doctype_pkey on c_doctype d (cost=0.14..5.49 rows=210 width=6) (actual time=0.005..0.018 rows=210 loops=1)

  • Heap Fetches: 0
18. 0.026 0.026 ↑ 1.0 1 13

Index Scan using c_bpartner_location_pkey on c_bpartner_location bpl (cost=0.29..1.02 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=13)

  • Index Cond: (c_bpartner_location_id = i.c_bpartner_location_id)
19. 0.026 0.026 ↑ 1.0 1 13

Index Only Scan using c_location_pkey on c_location loc (cost=0.29..0.31 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=13)

  • Index Cond: (c_location_id = bpl.c_location_id)
  • Heap Fetches: 0
20. 0.026 0.026 ↓ 0.0 0 13

Index Only Scan using idx_lbr_docfiscal_c_invoice_id on lbr_docfiscal df (cost=0.29..0.52 rows=1 width=6) (actual time=0.002..0.002 rows=0 loops=13)

  • Index Cond: (c_invoice_id = i.c_invoice_id)
  • Heap Fetches: 0
21. 404.268 9,330.886 ↑ 2.5 1,311 1

Nested Loop (cost=4,104.49..15,044.95 rows=3,218 width=57) (actual time=11.606..9,330.886 rows=1,311 loops=1)

22. 0.601 25.398 ↓ 1.4 2,876 1

Nested Loop (cost=4,104.06..5,528.74 rows=2,129 width=21) (actual time=10.960..25.398 rows=2,876 loops=1)

23. 0.005 0.005 ↑ 1.0 1 1

Index Only Scan using c_bpartner_pkey on c_bpartner bp_1 (cost=0.29..1.41 rows=1 width=6) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (c_bpartner_id = '5005922'::numeric)
  • Heap Fetches: 0
24. 1.669 24.792 ↓ 1.4 2,876 1

Nested Loop Left Join (cost=4,103.78..5,506.05 rows=2,129 width=21) (actual time=10.954..24.792 rows=2,876 loops=1)

25. 0.495 17.371 ↓ 1.4 2,876 1

Nested Loop (cost=4,103.49..4,648.59 rows=2,129 width=21) (actual time=10.949..17.371 rows=2,876 loops=1)

26. 0.002 0.002 ↑ 1.0 1 1

Index Only Scan using c_bpartner_pkey on c_bpartner b_1 (cost=0.29..1.41 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: (c_bpartner_id = '5005922'::numeric)
  • Heap Fetches: 0
27. 1.885 16.874 ↓ 1.4 2,876 1

Hash Join (cost=4,103.20..4,625.89 rows=2,129 width=21) (actual time=10.946..16.874 rows=2,876 loops=1)

  • Hash Cond: (i_1.c_doctype_id = d_1.c_doctype_id)
28. 3.376 14.946 ↓ 1.4 2,876 1

Hash Join (cost=4,095.08..4,612.07 rows=2,129 width=21) (actual time=10.900..14.946 rows=2,876 loops=1)

  • Hash Cond: (loc_1.c_location_id = bpl_1.c_location_id)
29. 1.635 1.635 ↑ 1.0 22,683 1

Index Only Scan using c_location_pkey on c_location loc_1 (cost=0.29..410.93 rows=22,683 width=6) (actual time=0.008..1.635 rows=22,683 loops=1)

  • Heap Fetches: 0
30. 0.409 9.935 ↓ 1.4 2,876 1

Hash (cost=4,068.18..4,068.18 rows=2,129 width=27) (actual time=9.935..9.935 rows=2,876 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 209kB
31. 0.670 9.526 ↓ 1.4 2,876 1

Hash Join (cost=1,110.28..4,068.18 rows=2,129 width=27) (actual time=6.761..9.526 rows=2,876 loops=1)

  • Hash Cond: (i_1.c_bpartner_location_id = bpl_1.c_bpartner_location_id)
32. 2.229 2.466 ↓ 1.4 2,876 1

Bitmap Heap Scan on c_invoice i_1 (cost=43.11..2,995.42 rows=2,129 width=39) (actual time=0.361..2.466 rows=2,876 loops=1)

  • Recheck Cond: (c_bpartner_id = '5005922'::numeric)
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ispayschedulevalid = 'Y'::bpchar) AND (docstatus = 'CO'::bpchar))
  • Rows Removed by Filter: 176
  • Heap Blocks: exact=1497
33. 0.237 0.237 ↑ 1.0 3,052 1

Bitmap Index Scan on c_invoice_documentno_target (cost=0.00..42.58 rows=3,128 width=0) (actual time=0.237..0.237 rows=3,052 loops=1)

  • Index Cond: (c_bpartner_id = '5005922'::numeric)
34. 2.784 6.390 ↑ 1.0 22,363 1

Hash (cost=787.63..787.63 rows=22,363 width=12) (actual time=6.390..6.390 rows=22,363 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1261kB
35. 3.606 3.606 ↑ 1.0 22,363 1

Seq Scan on c_bpartner_location bpl_1 (cost=0.00..787.63 rows=22,363 width=12) (actual time=0.003..3.606 rows=22,363 loops=1)

36. 0.025 0.043 ↑ 1.0 210 1

Hash (cost=5.49..5.49 rows=210 width=6) (actual time=0.043..0.043 rows=210 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
37. 0.018 0.018 ↑ 1.0 210 1

Index Only Scan using c_doctype_pkey on c_doctype d_1 (cost=0.14..5.49 rows=210 width=6) (actual time=0.005..0.018 rows=210 loops=1)

  • Heap Fetches: 0
38. 5.752 5.752 ↓ 0.0 0 2,876

Index Only Scan using idx_lbr_docfiscal_c_invoice_id on lbr_docfiscal df_1 (cost=0.29..0.39 rows=1 width=6) (actual time=0.002..0.002 rows=0 loops=2,876)

  • Index Cond: (c_invoice_id = i_1.c_invoice_id)
  • Heap Fetches: 0
39. 8,901.220 8,901.220 ↓ 0.0 0 2,876

Index Scan using idx_c_invoicepayschedule_c_invoice_id_c_invoicepayschedule_id on c_invoicepayschedule ips (cost=0.42..3.30 rows=3 width=20) (actual time=3.004..3.095 rows=0 loops=2,876)

  • Index Cond: (c_invoice_id = i_1.c_invoice_id)
  • Filter: ((isvalid = 'Y'::bpchar) AND (invoiceopen(i_1.c_invoice_id, c_invoicepayschedule_id) <> '0'::numeric))
  • Rows Removed by Filter: 2
40. 0.006 0.027 ↑ 1.0 33 1

Hash (cost=11.91..11.91 rows=33 width=6) (actual time=0.027..0.027 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
41. 0.013 0.021 ↑ 1.0 33 1

Bitmap Heap Scan on c_doctype dt (cost=1.50..11.91 rows=33 width=6) (actual time=0.010..0.021 rows=33 loops=1)

  • Recheck Cond: (docbasetype = 'ARI'::bpchar)
  • Heap Blocks: exact=8
42. 0.008 0.008 ↑ 1.0 33 1

Bitmap Index Scan on idx_c_doctype_docbasetype_lbr_hasopenitems_ad_org_id_ad_client_ (cost=0.00..1.49 rows=33 width=0) (actual time=0.008..0.008 rows=33 loops=1)

  • Index Cond: (docbasetype = 'ARI'::bpchar)
Planning time : 3.642 ms
Execution time : 9,366.455 ms