explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g3re

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 345.781 ↓ 0.0 0 1

Sort (cost=39,971,304.41..39,971,311.41 rows=2,800 width=339) (actual time=345.781..345.781 rows=0 loops=1)

  • Output: i.ad_org_id, o.name, o.value, (sum((sum(il.linenetamt)))), (sum(((sum(il.qtyinvoiced) * (SubPlan 2))))), (sum(((sum(il.linenetamt) - (sum(il.qtyinvoiced) * (SubPlan 3))))))
  • Sort Key: (sum(((sum(il.linenetamt) - (sum(il.qtyinvoiced) * (SubPlan 3))))))
  • Sort Method: quicksort Memory: 17kB
  • Buffers: shared hit=14453
2. 0.006 345.765 ↓ 0.0 0 1

HashAggregate (cost=39,971,095.10..39,971,144.10 rows=2,800 width=339) (actual time=345.765..345.765 rows=0 loops=1)

  • Output: i.ad_org_id, o.name, o.value, sum((sum(il.linenetamt))), sum(((sum(il.qtyinvoiced) * (SubPlan 2)))), sum(((sum(il.linenetamt) - (sum(il.qtyinvoiced) * (SubPlan 3)))))
  • Group Key: i.ad_org_id, o.name, o.value
  • Buffers: shared hit=14453
3. 0.001 345.759 ↓ 0.0 0 1

Hash Left Join (cost=40,719.47..39,971,036.54 rows=3,904 width=339) (actual time=345.759..345.759 rows=0 loops=1)

  • Output: i.ad_org_id, (sum(il.linenetamt)), ((sum(il.qtyinvoiced) * (SubPlan 2))), ((sum(il.linenetamt) - (sum(il.qtyinvoiced) * (SubPlan 3)))), o.name, o.value
  • Hash Cond: (i.ad_org_id = o.ad_org_id)
  • Buffers: shared hit=14453
4. 0.004 345.758 ↓ 0.0 0 1

HashAggregate (cost=40,718.16..39,970,978.81 rows=3,904 width=71) (actual time=345.758..345.758 rows=0 loops=1)

  • Output: i.ad_org_id, il.m_product_id, p.value, p.name, sum(il.qtyinvoiced), sum(il.linenetamt), (SubPlan 1), (sum(il.qtyinvoiced) * (SubPlan 2)), (sum(il.linenetamt) - (sum(il.qtyinvoiced) * (SubPlan 3)))
  • Group Key: il.m_product_id, p.value, p.name, i.ad_org_id
  • Buffers: shared hit=14453
5. 0.002 345.754 ↓ 0.0 0 1

Hash Left Join (cost=312.43..40,630.32 rows=3,904 width=71) (actual time=345.754..345.754 rows=0 loops=1)

  • Output: i.ad_org_id, il.m_product_id, il.qtyinvoiced, il.linenetamt, p.value, p.name
  • Hash Cond: (il.m_product_id = p.m_product_id)
  • Join Filter: (COALESCE(il.m_product_id, 0::numeric) <> 0::numeric)
  • Buffers: shared hit=14453
6. 0.001 345.752 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.42..40,235.35 rows=3,904 width=22) (actual time=345.752..345.752 rows=0 loops=1)

  • Output: i.ad_org_id, il.m_product_id, il.qtyinvoiced, il.linenetamt
  • Buffers: shared hit=14453
7. 345.751 345.751 ↓ 0.0 0 1

Seq Scan on xendra.c_invoice i (cost=0.00..26,193.69 rows=1,776 width=21) (actual time=345.751..345.751 rows=0 loops=1)

  • Output: 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, i.docstatus, i.docaction, i.processing, i.processed, i.posted, i.c_doctype_id, i (...)
  • Filter: ((i.docstatus <> 'DR'::bpchar) AND (i.docstatus <> 'VO'::bpchar) AND (i.isactive = 'Y'::bpchar) AND (i.issotrx = 'Y'::bpchar) AND (i.ad_client_id = 1000038::numeric) AND (date(i.dateinvoiced) >= '2020-03-01'::d (...)
  • Rows Removed by Filter: 361252
  • Buffers: shared hit=14453
8. 0.000 0.000 ↓ 0.0 0

Index Scan using c_invoiceline_invoice on xendra.c_invoiceline il (cost=0.42..7.87 rows=4 width=21) (never executed)

  • Output: il.c_invoiceline_id, il.ad_client_id, il.ad_org_id, il.isactive, il.created, il.createdby, il.updated, il.updatedby, il.c_invoice_id, il.c_orderline_id, il.m_inoutline_id, il.line, il.description, il.m_product_ (...)
  • Index Cond: (i.c_invoice_id = il.c_invoice_id)
  • Filter: (il.isactive = 'Y'::bpchar)
9. 0.000 0.000 ↓ 0.0 0

Hash (cost=245.89..245.89 rows=5,289 width=55) (never executed)

  • Output: p.value, p.name, p.m_product_id
10. 0.000 0.000 ↓ 0.0 0

Seq Scan on xendra.m_product p (cost=0.00..245.89 rows=5,289 width=55) (never executed)

  • Output: p.value, p.name, p.m_product_id
11.          

SubPlan (for HashAggregate)

12. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=7.54..3,404.16 rows=1 width=6) (never executed)

  • Output: ilne.priceactual
13. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=7.11..3,395.70 rows=1 width=20) (never executed)

  • Output: max(i_1.dateinvoiced), iln.m_product_id, max(iln.c_invoiceline_id)
  • Group Key: iln.m_product_id
14. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=7.11..3,395.68 rows=1 width=20) (never executed)

  • Output: iln.m_product_id, iln.c_invoiceline_id, i_1.dateinvoiced
15. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on xendra.c_invoiceline iln (cost=6.69..1,081.36 rows=292 width=18) (never executed)

  • Output: iln.c_invoiceline_id, iln.ad_client_id, iln.ad_org_id, iln.isactive, iln.created, iln.createdby, iln.updated, iln.updatedby, iln.c_invoice_id, iln.c_orderline_id, iln.m_inoutline_id, iln.line, iln.descr (...)
  • Recheck Cond: (iln.m_product_id = il.m_product_id)
16. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on c_invoiceline_product (cost=0.00..6.62 rows=292 width=0) (never executed)

  • Index Cond: (iln.m_product_id = il.m_product_id)
17. 0.000 0.000 ↓ 0.0 0

Index Scan using c_invoice_pkey on xendra.c_invoice i_1 (cost=0.42..7.92 rows=1 width=15) (never executed)

  • Output: i_1.c_invoice_id, i_1.ad_client_id, i_1.ad_org_id, i_1.isactive, i_1.created, i_1.createdby, i_1.updated, i_1.updatedby, i_1.issotrx, i_1.documentno, i_1.docstatus, i_1.docaction, i_1.processing, i_1.pr (...)
  • Index Cond: (i_1.c_invoice_id = iln.c_invoice_id)
  • Filter: ((i_1.issotrx = 'N'::bpchar) AND (date(i_1.dateinvoiced) <= '2020-03-15'::date))
18. 0.000 0.000 ↓ 0.0 0

Index Scan using c_invoiceline_pkey on xendra.c_invoiceline ilne (cost=0.42..8.44 rows=1 width=12) (never executed)

  • Output: ilne.c_invoiceline_id, ilne.ad_client_id, ilne.ad_org_id, ilne.isactive, ilne.created, ilne.createdby, ilne.updated, ilne.updatedby, ilne.c_invoice_id, ilne.c_orderline_id, ilne.m_inoutline_id, ilne.line, ilne.desc (...)
  • Index Cond: ((max(iln.c_invoiceline_id)) = ilne.c_invoiceline_id)
19. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=7.54..3,411.92 rows=2 width=6) (never executed)

  • Output: ilne_1.priceactual
20. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=7.11..3,395.00 rows=2 width=20) (never executed)

  • Output: max(i_2.dateinvoiced), iln_1.m_product_id, max(iln_1.c_invoiceline_id)
  • Group Key: iln_1.m_product_id
21. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=7.11..3,394.95 rows=4 width=20) (never executed)

  • Output: iln_1.m_product_id, iln_1.c_invoiceline_id, i_2.dateinvoiced
22. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on xendra.c_invoiceline iln_1 (cost=6.69..1,081.36 rows=292 width=18) (never executed)

  • Output: iln_1.c_invoiceline_id, iln_1.ad_client_id, iln_1.ad_org_id, iln_1.isactive, iln_1.created, iln_1.createdby, iln_1.updated, iln_1.updatedby, iln_1.c_invoice_id, iln_1.c_orderline_id, iln_1.m_inoutline_i (...)
  • Recheck Cond: (iln_1.m_product_id = il.m_product_id)
23. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on c_invoiceline_product (cost=0.00..6.62 rows=292 width=0) (never executed)

  • Index Cond: (iln_1.m_product_id = il.m_product_id)
24. 0.000 0.000 ↓ 0.0 0

Index Scan using c_invoice_pkey on xendra.c_invoice i_2 (cost=0.42..7.91 rows=1 width=15) (never executed)

  • Output: 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.pr (...)
  • Index Cond: (i_2.c_invoice_id = iln_1.c_invoice_id)
  • Filter: ((i_2.dateinvoiced <= '2020-03-15 20:49:09'::timestamp without time zone) AND (i_2.issotrx = 'N'::bpchar))
25. 0.000 0.000 ↓ 0.0 0

Index Scan using c_invoiceline_pkey on xendra.c_invoiceline ilne_1 (cost=0.42..8.44 rows=1 width=12) (never executed)

  • Output: ilne_1.c_invoiceline_id, ilne_1.ad_client_id, ilne_1.ad_org_id, ilne_1.isactive, ilne_1.created, ilne_1.createdby, ilne_1.updated, ilne_1.updatedby, ilne_1.c_invoice_id, ilne_1.c_orderline_id, ilne_1.m_inoutline_id (...)
  • Index Cond: ((max(iln_1.c_invoiceline_id)) = ilne_1.c_invoiceline_id)
26. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=7.54..3,411.92 rows=2 width=6) (never executed)

  • Output: ilne_2.priceactual
27. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=7.11..3,395.00 rows=2 width=20) (never executed)

  • Output: max(i_3.dateinvoiced), iln_2.m_product_id, max(iln_2.c_invoiceline_id)
  • Group Key: iln_2.m_product_id
28. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=7.11..3,394.95 rows=4 width=20) (never executed)

  • Output: iln_2.m_product_id, iln_2.c_invoiceline_id, i_3.dateinvoiced
29. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on xendra.c_invoiceline iln_2 (cost=6.69..1,081.36 rows=292 width=18) (never executed)

  • Output: iln_2.c_invoiceline_id, iln_2.ad_client_id, iln_2.ad_org_id, iln_2.isactive, iln_2.created, iln_2.createdby, iln_2.updated, iln_2.updatedby, iln_2.c_invoice_id, iln_2.c_orderline_id, iln_2.m_inoutline_i (...)
  • Recheck Cond: (iln_2.m_product_id = il.m_product_id)
30. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on c_invoiceline_product (cost=0.00..6.62 rows=292 width=0) (never executed)

  • Index Cond: (iln_2.m_product_id = il.m_product_id)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using c_invoice_pkey on xendra.c_invoice i_3 (cost=0.42..7.91 rows=1 width=15) (never executed)

  • Output: i_3.c_invoice_id, i_3.ad_client_id, i_3.ad_org_id, i_3.isactive, i_3.created, i_3.createdby, i_3.updated, i_3.updatedby, i_3.issotrx, i_3.documentno, i_3.docstatus, i_3.docaction, i_3.processing, i_3.pr (...)
  • Index Cond: (i_3.c_invoice_id = iln_2.c_invoice_id)
  • Filter: ((i_3.dateinvoiced <= '2020-03-15 20:49:09'::timestamp without time zone) AND (i_3.issotrx = 'N'::bpchar))
32. 0.000 0.000 ↓ 0.0 0

Index Scan using c_invoiceline_pkey on xendra.c_invoiceline ilne_2 (cost=0.42..8.44 rows=1 width=12) (never executed)

  • Output: ilne_2.c_invoiceline_id, ilne_2.ad_client_id, ilne_2.ad_org_id, ilne_2.isactive, ilne_2.created, ilne_2.createdby, ilne_2.updated, ilne_2.updatedby, ilne_2.c_invoice_id, ilne_2.c_orderline_id, ilne_2.m_inoutline_id (...)
  • Index Cond: ((max(iln_2.c_invoiceline_id)) = ilne_2.c_invoiceline_id)
33. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.14..1.14 rows=14 width=252) (never executed)

  • Output: o.name, o.value, o.ad_org_id
34. 0.000 0.000 ↓ 0.0 0

Seq Scan on xendra.ad_org o (cost=0.00..1.14 rows=14 width=252) (never executed)

  • Output: o.name, o.value, o.ad_org_id
Planning time : 5.116 ms
Execution time : 346.634 ms