explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HlWx

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 20,962.366 ↓ 0.0 0 1

GroupAggregate (cost=7,251,298.24..7,258,609.37 rows=63,575 width=661) (actual time=20,962.366..20,962.366 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
  • 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
  • Rows Removed by Filter: 8197580
2. 0.046 20,962.365 ↓ 0.0 0 1

Sort (cost=7,251,298.24..7,251,457.18 rows=63,575 width=661) (actual time=20,962.365..20,962.365 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. 5.786 20,962.319 ↓ 0.0 0 1

Hash Join (cost=6,956,981.14..7,227,536.17 rows=63,575 width=661) (actual time=20,962.319..20,962.319 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1".c_bpartner_id = bp.c_bpartner_id)
4. 0.001 20,956.533 ↓ 0.0 0 1

Nested Loop (cost=6,955,937.59..7,225,300.59 rows=63,575 width=636) (actual time=20,956.533..20,956.533 rows=0 loops=1)

5. 0.001 20,956.532 ↓ 0.0 0 1

Nested Loop Left Join (cost=6,955,937.16..7,111,882.23 rows=63,575 width=613) (actual time=20,956.532..20,956.532 rows=0 loops=1)

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

Merge Left Join (cost=6,955,936.73..6,977,253.17 rows=63,894 width=594) (actual time=20,956.531..20,956.531 rows=0 loops=1)

  • Merge Cond: ("*SELECT* 1".c_invoice_id = cl.c_invoice_id)
7. 0.007 20,956.531 ↓ 0.0 0 1

Sort (cost=6,510,902.13..6,511,061.87 rows=63,894 width=588) (actual time=20,956.531..20,956.531 rows=0 loops=1)

  • Sort Key: "*SELECT* 1".c_invoice_id
  • Sort Method: quicksort Memory: 25kB
8. 0.012 20,956.524 ↓ 0.0 0 1

Hash Join (cost=6,395,535.02..6,488,984.81 rows=63,894 width=588) (actual time=20,956.524..20,956.524 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1".c_currency_id = cr.c_currency_id)
9. 0.010 20,956.446 ↓ 0.0 0 1

Hash Join (cost=6,395,528.11..6,488,063.55 rows=73,441 width=584) (actual time=20,956.446..20,956.446 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1".c_paymentterm_id = pt.c_paymentterm_id)
10. 0.000 20,956.422 ↓ 0.0 0 1

Unique (cost=6,395,526.77..6,473,863.65 rows=979,211 width=284) (actual time=20,956.422..20,956.422 rows=0 loops=1)

11. 0.018 20,956.422 ↓ 0.0 0 1

Sort (cost=6,395,526.77..6,397,974.80 rows=979,211 width=284) (actual time=20,956.422..20,956.422 rows=0 loops=1)

  • Sort Method: quicksort Memory: 25kB
12. 0.001 20,956.404 ↓ 0.0 0 1

Append (cost=2,224.15..6,037,024.11 rows=979,211 width=284) (actual time=20,956.404..20,956.404 rows=0 loops=1)

13. 0.000 15,994.628 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=2,224.15..5,341,712.90 rows=979,210 width=284) (actual time=15,994.628..15,994.628 rows=0 loops=1)

14. 0.007 15,994.628 ↓ 0.0 0 1

Hash Join (cost=2,224.15..5,331,920.80 rows=979,210 width=284) (actual time=15,994.628..15,994.628 rows=0 loops=1)

  • Hash Cond: (i.c_paymentterm_id = p.c_paymentterm_id)
15. 0.045 15,994.609 ↓ 0.0 0 1

Hash Join (cost=2,222.81..3,825,160.08 rows=979,210 width=207) (actual time=15,994.609..15,994.609 rows=0 loops=1)

  • Hash Cond: (bpl.c_location_id = loc.c_location_id)
16. 0.065 15,988.426 ↓ 0.0 0 1

Hash Join (cost=1,638.70..2,804,973.55 rows=979,210 width=210) (actual time=15,988.426..15,988.426 rows=0 loops=1)

  • Hash Cond: (i.c_bpartner_location_id = bpl.c_bpartner_location_id)
17. 0.000 15,981.733 ↓ 0.0 0 1

Hash Join (cost=1,049.18..2,790,919.90 rows=979,210 width=209) (actual time=15,981.733..15,981.733 rows=0 loops=1)

  • Hash Cond: (i.c_bpartner_id = b.c_bpartner_id)
18. 0.002 15,981.733 ↓ 0.0 0 1

Hash Join (cost=5.63..2,776,412.21 rows=979,210 width=204) (actual time=15,981.733..15,981.733 rows=0 loops=1)

  • Hash Cond: (i.c_doctype_id = d.c_doctype_id)
19. 15,981.731 15,981.731 ↓ 0.0 0 1

Seq Scan on c_invoice i (cost=0.00..2,762,942.44 rows=979,210 width=206) (actual time=15,981.731..15,981.731 rows=0 loops=1)

  • Filter: ((ispayschedulevalid <> 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ad_client_id = '1000000'::numeric) AND (issotrx = 'Y'::bpchar) AND ((c_bpartner_id = '1000268'::numeric) OR (pay_bpartner_id = '1000268'::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[])) AND (invoiceopen(c_invoice_id, '0'::numeric) <> '0'::numeric))
20. 0.000 0.000 ↓ 0.0 0

Hash (cost=4.17..4.17 rows=117 width=10) (never executed)

21. 0.000 0.000 ↓ 0.0 0

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

22. 0.000 0.000 ↓ 0.0 0

Hash (cost=893.80..893.80 rows=11,980 width=13) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_bpartner b (cost=0.00..893.80 rows=11,980 width=13) (never executed)

24. 2.759 6.628 ↑ 1.0 14,582 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 819kB
25. 3.869 3.869 ↑ 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..3.869 rows=14,582 loops=1)

26. 2.750 6.138 ↑ 1.0 15,129 1

Hash (cost=394.05..394.05 rows=15,205 width=13) (actual time=6.138..6.138 rows=15,129 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 750kB
27. 3.388 3.388 ↑ 1.0 15,129 1

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

28. 0.007 0.012 ↑ 1.0 15 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.005 0.005 ↑ 1.0 15 1

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

30. 0.001 4,961.775 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..695,311.21 rows=1 width=284) (actual time=4,961.775..4,961.775 rows=0 loops=1)

31. 0.001 4,961.774 ↓ 0.0 0 1

Nested Loop (cost=0.00..695,311.20 rows=1 width=284) (actual time=4,961.774..4,961.774 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))
32. 0.000 4,961.773 ↓ 0.0 0 1

Nested Loop (cost=0.00..695,308.64 rows=1 width=207) (actual time=4,961.773..4,961.773 rows=0 loops=1)

33. 0.000 4,961.773 ↓ 0.0 0 1

Join Filter: (bpl_1.c_location_id = loc_1.c_location_id)-> Nested Loop (cost=0.00..694,723.50 rows=1 width=210) (actual time=4,961.773..4,961.773 rows=0 loops=1)

  • Join Filter: (i_1.c_bpartner_location_id = bpl_1.c_bpartner_location_id)
34. 0.000 4,961.773 ↓ 0.0 0 1

Nested Loop (cost=0.00..694,133.98 rows=1 width=209) (actual time=4,961.773..4,961.773 rows=0 loops=1)

  • Join Filter: (i_1.c_bpartner_id = b_1.c_bpartner_id)
35. 0.002 4,961.773 ↓ 0.0 0 1

Nested Loop (cost=0.00..693,090.43 rows=1 width=204) (actual time=4,961.773..4,961.773 rows=0 loops=1)

  • Join Filter: (i_1.c_doctype_id = d_1.c_doctype_id)
36. 4,961.771 4,961.771 ↓ 0.0 0 1

Seq Scan on c_invoice i_1 (cost=0.00..693,084.80 rows=1 width=206) (actual time=4,961.771..4,961.771 rows=0 loops=1)

  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ispayschedulevalid = 'Y'::bpchar) AND (issotrx = 'Y'::bpchar) AND (ad_client_id = '1000000'::numeric) AND ((c_bpartner_id = '1000268'::numeric) OR (pay_bpartner_id = '1000268'::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: 8197580
37. 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)

38. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_bpartner b_1 (cost=0.00..893.80 rows=11,980 width=13) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_bpartner_location bpl_1 (cost=0.00..405.34 rows=14,734 width=16) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_location loc_1 (cost=0.00..394.05 rows=15,205 width=13) (never executed)

41. 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)
42. 0.006 0.014 ↑ 1.0 15 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.008 0.008 ↑ 1.0 15 1

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

44. 0.066 0.066 ↑ 1.0 174 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
45. 0.000 0.032 ↑ 1.0 174 1

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

46. 0.000 0.000 ↓ 0.0 0

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

47. 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
48. 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)

49. 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)
50. 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)
51. 2.490 5.743 ↑ 1.0 11,980 1

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

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

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