explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cFHr

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 27,259.907 ↓ 0.0 0 1

GroupAggregate (cost=4,845,740.42..4,853,054.19 rows=63,598 width=661) (actual time=27,259.907..27,259.907 rows=0 loops=1)

  • Group Key: "*SELECT* 1".dateinvoiced, "*SELECT* 1".documentno, "*SELECT* 1".c_invoice_id, "*SELECT* 1".c_order_id, "*SELECT* 1".issotrx, "*SELECT* 1".netdays, "*SELECT* 1".duedate, "*SELECT* 1".daysdue, (("*SELECT* 1".discountdate)::timestamp without time zone), "*SELECT* 1".discountamt, inv.grandtotal, "*SELECT* 1".paidamt, "*SELECT* 1".openamt, "*SELECT* 1".c_currency_id, "*SELECT* 1".c_paymentterm_id, (NULL::numeric), ord.documentno, cr.iso_code, pt.name, ((((bp.name)::text || ' '::text) || (bp.name2)::text)), "*SELECT* 1".c_bpartner_id, ord.poreference, inv.poreference, inv.c_charge_id, pt.discount
  • Hash Cond: ("*SELECT* 1".c_paymentterm_id = pt.c_paymentterm_id)
  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
2. 0.012 27,259.905 ↓ 0.0 0 1

Sort (cost=4,845,740.42..4,845,899.42 rows=63,598 width=661) (actual time=27,259.905..27,259.905 rows=0 loops=1)

  • Sort Key: "*SELECT* 1".dateinvoiced, "*SELECT* 1".documentno, "*SELECT* 1".c_invoice_id, "*SELECT* 1".c_order_id, "*SELECT* 1".issotrx, "*SELECT* 1".netdays, "*SELECT* 1".duedate, "*SELECT* 1".daysdue, (("*SELECT* 1".discountdate)::timestamp without time zone), "*SELECT* 1".discountamt, inv.grandtotal, "*SELECT* 1".paidamt, "*SELECT* 1".openamt, "*SELECT* 1".c_currency_id, "*SELECT* 1".c_paymentterm_id, (NULL::numeric), ord.documentno, cr.iso_code, pt.name, ((((bp.name)::text || ' '::text) || (bp.name2)::text)), "*SELECT* 1".c_bpartner_id, ord.poreference, inv.poreference, inv.c_charge_id, pt.discount
  • Sort Method: quicksort Memory: 25kB
3. 482.690 27,259.893 ↓ 0.0 0 1

Hash Join (cost=4,551,392.05..4,821,969.35 rows=63,598 width=661) (actual time=27,259.893..27,259.893 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1".c_bpartner_id = bp.c_bpartner_id)
4. 0.000 26,777.203 ↓ 0.0 0 1

Nested Loop (cost=4,550,348.50..4,819,733.34 rows=63,598 width=636) (actual time=26,777.203..26,777.203 rows=0 loops=1)

5. 0.001 26,777.203 ↓ 0.0 0 1

Nested Loop Left Join (cost=4,550,348.06..4,706,305.06 rows=63,598 width=613) (actual time=26,777.203..26,777.203 rows=0 loops=1)

  • Filter: ((COALESCE(ord.ordertype, ''::character varying))::text <> 'Lay Buy Order'::text)
6. 0.002 26,777.202 ↓ 0.0 0 1

Merge Left Join (cost=4,550,347.63..4,571,664.19 rows=63,918 width=594) (actual time=26,777.202..26,777.202 rows=0 loops=1)

  • Merge Cond: ("*SELECT* 1".c_invoice_id = cl.c_invoice_id)
7. 0.021 26,777.200 ↓ 0.0 0 1

Sort (cost=4,105,313.04..4,105,472.83 rows=63,918 width=588) (actual time=26,777.200..26,777.200 rows=0 loops=1)

  • Sort Key: "*SELECT* 1".c_invoice_id
  • Sort Method: quicksort Memory: 25kB
8. 0.124 26,777.179 ↓ 0.0 0 1

Hash Join (cost=3,989,901.43..4,083,386.62 rows=63,918 width=588) (actual time=26,777.179..26,777.179 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1".c_currency_id = cr.c_currency_id)
9. 0.005 26,777.055 ↓ 0.0 0 1

Hash Join (cost=3,989,894.52..4,082,465.02 rows=73,469 width=584) (actual time=26,777.055..26,777.055 rows=0 loops=1)

10. 0.001 26,777.035 ↓ 0.0 0 1

Unique (cost=3,989,893.18..4,068,259.74 rows=979,582 width=284) (actual time=26,777.035..26,777.035 rows=0 loops=1)

11. 0.021 26,777.034 ↓ 0.0 0 1

Sort (cost=3,989,893.18..3,992,342.13 rows=979,582 width=284) (actual time=26,777.034..26,777.034 rows=0 loops=1)

  • Sort Key: "*SELECT* 1".ad_org_id, "*SELECT* 1".ad_client_id, "*SELECT* 1".documentno, "*SELECT* 1".c_invoice_id, "*SELECT* 1".c_order_id, "*SELECT* 1".c_bpartner_id, "*SELECT* 1".issotrx, "*SELECT* 1".dateinvoiced, "*SELECT* 1".dateacct, "*SELECT* 1".netdays, "*SELECT* 1".duedate, "*SELECT* 1".daysdue, (("*SELECT* 1".discountdate)::timestamp without time zone), "*SELECT* 1".discountamt, "*SELECT* 1".grandtotal, "*SELECT* 1".paidamt, "*SELECT* 1".openamt, "*SELECT* 1".c_currency_id, "*SELECT* 1".c_conversiontype_id, "*SELECT* 1".c_paymentterm_id, "*SELECT* 1".ispayschedulevalid, (NULL::numeric), "*SELECT* 1".invoicecollectiontype, "*SELECT* 1".c_campaign_id, "*SELECT* 1".c_project_id, "*SELECT* 1".c_activity_id, "*SELECT* 1".c_region_id, "*SELECT* 1".salesrep_id, "*SELECT* 1".c_bp_group_id, "*SELECT* 1".pay_bpartner_id, "*SELECT* 1".poreference
  • Sort Method: quicksort Memory: 25kB
12. 0.003 26,777.013 ↓ 0.0 0 1

Append (cost=27,464.48..3,631,252.92 rows=979,582 width=284) (actual time=26,777.013..26,777.013 rows=0 loops=1)

13. 0.000 23,096.246 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=27,464.48..3,259,812.55 rows=979,581 width=284) (actual time=23,096.246..23,096.246 rows=0 loops=1)

14. 0.003 23,096.246 ↓ 0.0 0 1

Hash Join (cost=27,464.48..3,250,016.74 rows=979,581 width=284) (actual time=23,096.246..23,096.246 rows=0 loops=1)

  • Hash Cond: (i.c_paymentterm_id = p.c_paymentterm_id)
15. 0.032 23,096.229 ↓ 0.0 0 1

Hash Join (cost=27,463.14..1,742,685.14 rows=979,581 width=207) (actual time=23,096.229..23,096.229 rows=0 loops=1)

  • Hash Cond: (i.c_bpartner_id = b.c_bpartner_id)
16. 0.011 23,088.061 ↓ 0.0 0 1

Hash Join (cost=26,419.59..721,652.87 rows=979,581 width=202) (actual time=23,088.061..23,088.061 rows=0 loops=1)

  • Hash Cond: (i.c_doctype_id = d.c_doctype_id)
17. 0.014 23,087.967 ↓ 0.0 0 1

Hash Join (cost=26,413.96..708,178.00 rows=979,581 width=204) (actual time=23,087.967..23,087.967 rows=0 loops=1)

  • Hash Cond: (i.c_bpartner_location_id = bpl.c_bpartner_location_id)
18. 22,258.536 22,869.993 ↓ 0.0 0 1

Bitmap Heap Scan on c_invoice i (cost=25,041.86..693,336.67 rows=979,581 width=206) (actual time=22,869.993..22,869.993 rows=0 loops=1)

  • Recheck Cond: ((c_bpartner_id = '1000268'::numeric) OR (pay_bpartner_id = '1000268'::numeric))Rows Removed by Index Recheck: 2999602
  • Filter: ((ispayschedulevalid <> 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ad_client_id = '1000000'::numeric) AND (issotrx = 'Y'::bpchar) AND (dateinvoiced <= to_timestamp('2020-02-06 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (ad_org_id = ANY ('{0,1000020,1000009,1000003,10015300,1000015,1000006,1000014,11893001,1000012,1000013,1000005,11893000,1000004,1000000,1000021}'::numeric[])) AND (invoiceopen(c_invoice_id, '0'::numeric) <> '0'::numeric))
  • Rows Removed by Filter: 1270948
  • Heap Blocks: exact=55436 lossy=134884
19. 0.001 611.457 ↓ 0.0 0 1

BitmapOr (cost=25,041.86..25,041.86 rows=1,273,001 width=0) (actual time=611.457..611.457 rows=0 loops=1)

20. 611.452 611.452 ↑ 1.0 1,270,949 1

Bitmap Index Scan on c_invoice_bpartner (cost=0.00..24,547.94 rows=1,273,001 width=0) (actual time=611.452..611.452 rows=1,270,949 loops=1)

  • Index Cond: (c_bpartner_id = '1000268'::numeric)
21. 0.004 0.004 ↓ 0.0 0 1

Bitmap Index Scan on paybpartnerinvoice (cost=0.00..4.13 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (pay_bpartner_id = '1000268'::numeric)
22. 3.164 217.960 ↑ 1.0 14,582 1

Hash (cost=1,187.92..1,187.92 rows=14,734 width=13) (actual time=217.960..217.960 rows=14,582 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 728kB
23. 8.948 214.796 ↑ 1.0 14,582 1

Hash Join (cost=589.52..1,187.92 rows=14,734 width=13) (actual time=16.673..214.796 rows=14,582 loops=1)

  • Hash Cond: (loc.c_location_id = bpl.c_location_id)
24. 189.204 189.204 ↑ 1.0 15,129 1

Seq Scan on c_location loc (cost=0.00..394.05 rows=15,205 width=13) (actual time=0.005..189.204 rows=15,129 loops=1)

25. 2.608 16.644 ↑ 1.0 14,582 1

Hash (cost=405.34..405.34 rows=14,734 width=16) (actual time=16.644..16.644 rows=14,582 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 819kB
26. 14.036 14.036 ↑ 1.0 14,582 1

Seq Scan on c_bpartner_location bpl (cost=0.00..405.34 rows=14,734 width=16) (actual time=0.005..14.036 rows=14,582 loops=1)

27. 0.031 0.083 ↑ 1.0 117 1

Hash (cost=4.17..4.17 rows=117 width=10) (actual time=0.083..0.083 rows=117 loops=1)

28. 0.052 0.052 ↑ 1.0 117 1

Seq Scan on c_doctype d (cost=0.00..4.17 rows=117 width=10) (actual time=0.005..0.052 rows=117 loops=1)

29. 3.215 8.136 ↑ 1.0 11,980 1

Hash (cost=893.80..893.80 rows=11,980 width=13) (actual time=8.136..8.136 rows=11,980 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 672kB
30. 4.921 4.921 ↑ 1.0 11,980 1

Seq Scan on c_bpartner b (cost=0.00..893.80 rows=11,980 width=13) (actual time=0.003..4.921 rows=11,980 loops=1)

31. 0.008 0.014 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=80) (actual time=0.014..0.014 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.006 0.006 ↑ 1.0 15 1

Seq Scan on c_paymentterm p (cost=0.00..1.15 rows=15 width=80) (actual time=0.002..0.006 rows=15 loops=1)

33. 0.000 3,680.764 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=24,552.93..371,440.37 rows=1 width=284) (actual time=3,680.764..3,680.764 rows=0 loops=1)

34. 0.001 3,680.764 ↓ 0.0 0 1

Nested Loop (cost=24,552.93..371,440.36 rows=1 width=284) (actual time=3,680.764..3,680.764 rows=0 loops=1)

  • Join Filter: ((i_1.c_invoice_id = ips.c_invoice_id) AND (invoiceopen(i_1.c_invoice_id, ips.c_invoicepayschedule_id) <> '0'::numeric))
35. 0.001 3,680.763 ↓ 0.0 0 1

Nested Loop (cost=24,552.93..371,437.80 rows=1 width=207) (actual time=3,680.763..3,680.763 rows=0 loops=1)

36. 0.000 3,680.762 ↓ 0.0 0 1

Nested Loop (cost=24,552.64..371,436.38 rows=1 width=210) (actual time=3,680.762..3,680.762 rows=0 loops=1)

37. 0.000 3,680.762 ↓ 0.0 0 1

Nested Loop (cost=24,552.36..371,428.07 rows=1 width=209) (actual time=3,680.762..3,680.762 rows=0 loops=1)

38. 0.002 3,680.762 ↓ 0.0 0 1

Nested Loop (cost=24,552.07..371,419.76 rows=1 width=204) (actual time=3,680.762..3,680.762 rows=0 loops=1)

  • Join Filter: (i_1.c_doctype_id = d_1.c_doctype_id)
39. 3,493.739 3,680.760 ↓ 0.0 0 1

Bitmap Heap Scan on c_invoice i_1 (cost=24,552.07..371,414.12 rows=1 width=206) (actual time=3,680.760..3,680.760 rows=0 loops=1)

  • Recheck Cond: ((c_bpartner_id = '1000268'::numeric) OR (pay_bpartner_id = '1000268'::numeric))Rows Removed by Index Recheck: 2999602
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ispayschedulevalid = 'Y'::bpchar) AND (issotrx = 'Y'::bpchar) AND (ad_client_id = '1000000'::numeric) AND (dateinvoiced <= to_timestamp('2020-02-06 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (ad_org_id = ANY ('{0,1000020,1000009,1000003,10015300,1000015,1000006,1000014,11893001,1000012,1000013,1000005,11893000,1000004,1000000,1000021}'::numeric[])))
  • Rows Removed by Filter: 1270948
  • Heap Blocks: exact=55436 lossy=134884
40. 0.002 187.021 ↓ 0.0 0 1

BitmapOr (cost=24,552.07..24,552.07 rows=1,273,001 width=0) (actual time=187.021..187.021 rows=0 loops=1)

41. 187.016 187.016 ↑ 1.0 1,270,949 1

Bitmap Index Scan on c_invoice_bpartner (cost=0.00..24,547.94 rows=1,273,001 width=0) (actual time=187.016..187.016 rows=1,270,949 loops=1)

  • Index Cond: (c_bpartner_id = '1000268'::numeric)
42. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on paybpartnerinvoice (cost=0.00..4.13 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (pay_bpartner_id = '1000268'::numeric)
43. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_doctype d_1 (cost=0.00..4.17 rows=117 width=10) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Index Scan using c_bpartner_pkey on c_bpartner b_1 (cost=0.29..8.30 rows=1 width=13) (never executed)

  • Index Cond: (c_bpartner_id = i_1.c_bpartner_id)
45. 0.000 0.000 ↓ 0.0 0

Index Scan using c_bpartner_location_pkey on c_bpartner_location bpl_1 (cost=0.29..8.30 rows=1 width=16) (never executed)

  • Index Cond: (c_bpartner_location_id = i_1.c_bpartner_location_id)
46. 0.000 0.000 ↓ 0.0 0

Index Scan using c_location_pkey on c_location loc_1 (cost=0.29..0.38 rows=1 width=13) (never executed)

  • Index Cond: (c_location_id = bpl_1.c_location_id)
47. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_invoicepayschedule ips (cost=0.00..1.02 rows=1 width=112) (never executed)

  • Filter: (isvalid = 'Y'::bpchar)
48. 0.015 0.015 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=186) (actual time=0.015..0.015 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 0.000 0.009 ↑ 1.0 15 1

Seq Scan on c_paymentterm pt (cost=0.00..1.15 rows=15 width=186) (actual time=0.003..0.009 rows=15 loops=1)

50. 0.047 0.116 ↑ 1.0 174 1

Hash (cost=4.74..4.74 rows=174 width=9) (actual time=0.116..0.116 rows=174 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
51. 0.069 0.069 ↑ 1.0 174 1

Seq Scan on c_currency cr (cost=0.00..4.74 rows=174 width=9) (actual time=0.005..0.069 rows=174 loops=1)

52. 0.000 0.000 ↓ 0.0 0

Materialize (cost=445,034.59..459,032.57 rows=2,799,595 width=22) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Sort (cost=445,034.59..452,033.58 rows=2,799,595 width=22) (never executed)

  • Sort Key: cl.c_invoice_id
54. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_cashline cl (cost=0.00..87,828.95 rows=2,799,595 width=22) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Index Scan using c_order_pkey on c_order ord (cost=0.43..2.09 rows=1 width=37) (never executed)

  • Index Cond: ("*SELECT* 1".c_order_id = c_order_id)
56. 0.000 0.000 ↓ 0.0 0

Index Scan using c_invoice_key on c_invoice inv (cost=0.43..1.77 rows=1 width=31) (never executed)

  • Index Cond: (c_invoice_id = "*SELECT* 1".c_invoice_id)
57. 3.041 482.674 ↑ 1.0 11,980 1

Hash (cost=893.80..893.80 rows=11,980 width=33) (actual time=482.674..482.674 rows=11,980 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 846kB
58. 479.633 479.633 ↑ 1.0 11,980 1

Seq Scan on c_bpartner bp (cost=0.00..893.80 rows=11,980 width=33) (actual time=0.003..479.633 rows=11,980 loops=1)