explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mJoW

Settings
# exclusive inclusive rows x rows loops node
1. 86,145.973 86,288.104 ↑ 1.0 1 1

Index Only Scan using c_bpartner_pkey on c_bpartner bp (cost=0.29..145,557.04 rows=1 width=8) (actual time=86,288.099..86,288.104 rows=1 loops=1)

  • Index Cond: (c_bpartner_id = '1000034'::numeric)
  • Heap Fetches: 2
2. 0.000 0.005 ↓ 2.0 2 1

Seq Scan on c_invoicepayschedule ips (cost=0.00..1.02 rows=1 width=72) (actual time=0.004..0.005 rows=2 loops=1)

  • Filter: (isvalid = 'Y'::bpchar)
3. 0.018 0.018 ↓ 0.0 0 2

Index Scan using c_invoice_key on c_invoice i_1 (cost=0.43..8.46 rows=1 width=287) (actual time=0.009..0.009 rows=0 loops=2)

  • Index Cond: (c_invoice_id = ips.c_invoice_id)
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ispayschedulevalid = 'Y'::bpchar) AND (issotrx = 'Y'::bpchar) AND (c_bpartner_id = bp.c_bpartner_id) AND (ispaid = 'N'::bpchar))
  • Rows Removed by Filter: 1
4.          

SubPlan (for Seq Scan)

5. 76,986.259 85,839.709 ↑ 1.0 1 1

Aggregate (cost=44,530.53..44,530.54 rows=1 width=136) (actual time=85,839.709..85,839.709 rows=1 loops=1)

6. 736.117 8,853.450 ↓ 21.4 167,558 1

HashAggregate (cost=40,406.53..40,484.71 rows=7,818 width=306) (actual time=8,661.508..8,853.450 rows=167,558 loops=1)

  • Group Key: i_2.c_invoice_id, i_2.ad_client_id, i_2.ad_org_id, i_2.isactive, i_2.created, i_2.createdby, i_2.updated, i_2.updatedby, i_2.issotrx, i_2.documentno, i_2.docstatus, i_2.docaction, i_2.processing, i_2.processed, i_2.c_doctype_id, i_2.c_doctypetarget_id, i_2.c_order_id, i_2.description, i_2.isapproved, i_2.istransferred, i_2.salesrep_id, i_2.dateinvoiced, i_2.dateprinted, i_2.dateacct, i_2.c_bpartner_id, i_2.c_bpartner_location_id, i_2.ad_user_id, i_2.poreference, i_2.dateordered, i_2.c_currency_id, i_2.c_conversiontype_id, i_2.paymentrule, i_2.c_paymentterm_id, i_2.c_charge_id, i_2.m_pricelist_id, i_2.c_campaign_id, i_2.c_project_id, i_2.c_activity_id, i_2.isprinted, i_2.isdiscountprinted, i_2.ispaid, i_2.isindispute, i_2.ispayschedulevalid, (NULL::numeric), i_2.invoicecollectiontype, i_2.dunninggrace, (CASE WHEN ((charat((d_2.docbasetype)::character varying, 3))::text = 'C'::text) THEN (i_2.chargeamt * '-1'::numeric) ELSE i_2.chargeamt END), (CASE WHEN ((charat((d_2.docbasetype)::character varying, 3))::text = 'C'::text) THEN (i_2.totallines * '-1'::numeric) ELSE i_2.totallines END), (CASE WHEN ((charat((d_2.docbasetype)::character varying, 3))::text = 'C'::text) THEN (i_2.grandtotal * '-1'::numeric) ELSE i_2.grandtotal END), (CASE WHEN ((charat((d_2.docbasetype)::character varying, 3))::text = 'C'::text) THEN '-1.0'::numeric ELSE 1.0 END), (CASE WHEN ((charat((d_2.docbasetype)::character varying, 2))::text = 'P'::text) THEN '-1.0'::numeric ELSE 1.0 END), d_2.docbasetype, (paymenttermduedate(i_2.c_paymentterm_id, (i_2.dateinvoiced)::timestamp with time zone))
7. 24.866 8,117.333 ↓ 21.4 167,558 1

Append (cost=6.07..39,370.64 rows=7,818 width=306) (actual time=0.238..8,117.333 rows=167,558 loops=1)

8. 7,833.311 8,092.467 ↓ 21.4 167,558 1

Hash Join (cost=6.07..39,277.57 rows=7,817 width=306) (actual time=0.238..8,092.467 rows=167,558 loops=1)

  • Hash Cond: (i_2.c_doctype_id = d_2.c_doctype_id)
9. 259.115 259.115 ↓ 21.4 167,558 1

Index Scan using c_invoice_bpartner on c_invoice i_2 (cost=0.43..27,165.36 rows=7,817 width=302) (actual time=0.017..259.115 rows=167,558 loops=1)

  • Index Cond: (c_bpartner_id = bp.c_bpartner_id)
  • Filter: ((ispayschedulevalid <> 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL}'::bpchar[])))
10. 0.017 0.041 ↑ 1.0 117 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
11. 0.024 0.024 ↑ 1.0 117 1

Seq Scan on c_doctype d_2 (cost=0.00..4.17 rows=117 width=10) (actual time=0.002..0.024 rows=117 loops=1)

12. 0.000 0.038 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=9.50..14.90 rows=1 width=347) (actual time=0.038..0.038 rows=0 loops=1)

13. 0.007 0.035 ↓ 0.0 0 1

Hash Join (cost=9.50..14.89 rows=1 width=347) (actual time=0.035..0.035 rows=0 loops=1)

  • Hash Cond: (d_3.c_doctype_id = i_3.c_doctype_id)
14. 0.002 0.002 ↑ 117.0 1 1

Seq Scan on c_doctype d_3 (cost=0.00..4.17 rows=117 width=10) (actual time=0.002..0.002 rows=1 loops=1)

15. 0.003 0.026 ↓ 0.0 0 1

Hash (cost=9.49..9.49 rows=1 width=343) (actual time=0.026..0.026 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
16. 0.001 0.023 ↓ 0.0 0 1

Nested Loop (cost=0.43..9.49 rows=1 width=343) (actual time=0.023..0.023 rows=0 loops=1)

17. 0.004 0.004 ↓ 2.0 2 1

Seq Scan on c_invoicepayschedule ips_1 (cost=0.00..1.02 rows=1 width=72) (actual time=0.003..0.004 rows=2 loops=1)

  • Filter: (isvalid = 'Y'::bpchar)
18. 0.018 0.018 ↓ 0.0 0 2

Index Scan using c_invoice_key on c_invoice i_3 (cost=0.43..8.46 rows=1 width=287) (actual time=0.009..0.009 rows=0 loops=2)

  • Index Cond: (c_invoice_id = ips_1.c_invoice_id)
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ispayschedulevalid = 'Y'::bpchar) AND (c_bpartner_id = bp.c_bpartner_id))
  • Rows Removed by Filter: 1
19.          

SubPlan (for Subquery Scan)

20. 0.006 306.277 ↑ 1.0 1 1

Aggregate (cost=73,766.26..73,766.27 rows=1 width=32) (actual time=306.276..306.277 rows=1 loops=1)

21. 279.052 306.271 ↓ 0.0 0 1

Bitmap Heap Scan on c_payment (cost=636.59..73,761.71 rows=9 width=32) (actual time=306.271..306.271 rows=0 loops=1)

  • Recheck Cond: (c_bpartner_id = bp.c_bpartner_id)
  • Rows Removed by Index Recheck: 336176
  • Filter: ((c_charge_id IS NULL) AND (docstatus = ANY ('{CO,CL}'::bpchar[])) AND (isallocated = 'N'::bpchar))
  • Rows Removed by Filter: 167793
  • Heap Blocks: exact=45558 lossy=34174
22. 27.219 27.219 ↓ 7.7 167,793 1

Bitmap Index Scan on c_payment_bpartner (cost=0.00..636.59 rows=21,887 width=0) (actual time=27.219..27.219 rows=167,793 loops=1)

  • Index Cond: (c_bpartner_id = bp.c_bpartner_id)
23.          

SubPlan (for Index Only Scan)

24. 0.003 142.088 ↑ 1.0 1 1

Aggregate (cost=27,251.92..27,251.93 rows=1 width=104) (actual time=142.088..142.088 rows=1 loops=1)

25. 0.001 142.085 ↓ 0.0 0 1

HashAggregate (cost=27,247.71..27,247.79 rows=8 width=311) (actual time=142.085..142.085 rows=0 loops=1)

  • Group Key: i.c_invoice_id, i.ad_client_id, i.ad_org_id, i.isactive, i.created, i.createdby, i.updated, i.updatedby, i.issotrx, i.documentno,
26. 0.004 142.084 ↓ 0.0 0 1

Append (cost=0.43..27,246.65 rows=8 width=311) (actual time=142.084..142.084 rows=0 loops=1)

27. 0.000 142.033 ↓ 0.0 0 1

Nested Loop (cost=0.43..27,231.68 rows=7 width=306) (actual time=142.033..142.033 rows=0 loops=1)

  • Join Filter: (i.c_doctype_id = d.c_doctype_id)
28. 0.010 0.010 ↑ 1.0 117 1

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

29. 0.038 142.038 ↓ 0.0 0 117

Materialize (cost=0.43..27,204.50 rows=7 width=302) (actual time=1.214..1.214 rows=0 loops=117)

30. 142.000 142.000 ↓ 0.0 0 1

Index Scan using c_invoice_bpartner on c_invoice i (cost=0.43..27,204.46 rows=7 width=302) (actual time=142.000..142.000 rows=0 loops=1)

  • Index Cond: (c_bpartner_id = bp.c_bpartner_id)
  • Filter: ((ispayschedulevalid <> 'Y'::bpchar) AND (docstatus = ANY ('{CO,CL}'::bpchar[])) AND (issotrx = 'Y'::bpchar) AND (ispaid = 'N'::bpchar))
  • Rows Removed by Filter: 167558
31. 0.003 0.047 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=9.51..14.91 rows=1 width=347) (actual time=0.047..0.047 rows=0 loops=1)

32. 0.014 0.044 ↓ 0.0 0 1

Hash Join (cost=9.51..14.90 rows=1 width=347) (actual time=0.044..0.044 rows=0 loops=1)

  • Hash Cond: (d_1.c_doctype_id = i_1.c_doctype_id)
33. 0.002 0.002 ↑ 117.0 1 1

Seq Scan on c_doctype d_1 (cost=0.00..4.17 rows=117 width=10) (actual time=0.002..0.002 rows=1 loops=1)

34. 0.003 0.028 ↓ 0.0 0 1

Hash (cost=9.50..9.50 rows=1 width=343) (actual time=0.028..0.028 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
35. 0.025 0.025 ↓ 0.0 0 1

Nested Loop (cost=0.43..9.50 rows=1 width=343) (actual time=0.025..0.025 rows=0 loops=1)