explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qkQp

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 143,857.151 ↑ 33.5 10 1

Sort (cost=61,557,104.12..61,557,104.96 rows=335 width=339) (actual time=143,857.151..143,857.151 rows=10 loops=1)

  • Output: il.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=96681873 read=21039
2. 28.061 143,857.126 ↑ 33.5 10 1

HashAggregate (cost=61,557,084.21..61,557,090.07 rows=335 width=339) (actual time=143,857.120..143,857.126 rows=10 loops=1)

  • Output: il.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: il.ad_org_id, o.name, o.value
  • Buffers: shared hit=96681873 read=21039
3. 20.786 143,829.065 ↓ 21.7 7,265 1

Hash Join (cost=68,128.19..61,557,079.18 rows=335 width=339) (actual time=541.748..143,829.065 rows=7,265 loops=1)

  • Output: il.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: (il.ad_org_id = o.ad_org_id)
  • Buffers: shared hit=96681873 read=21039
4. 126.970 143,808.241 ↓ 1.5 7,265 1

HashAggregate (cost=68,126.88..61,557,008.79 rows=4,780 width=22) (actual time=541.689..143,808.241 rows=7,265 loops=1)

  • Output: il.ad_org_id, il.m_product_id, 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, il.ad_org_id
  • Buffers: shared hit=96681872 read=21039
5. 18.913 502.651 ↓ 5.6 26,946 1

Nested Loop (cost=0.42..68,043.23 rows=4,780 width=22) (actual time=6.971..502.651 rows=26,946 loops=1)

  • Output: il.ad_org_id, il.m_product_id, il.qtyinvoiced, il.linenetamt
  • Buffers: shared hit=77614 read=17094
6. 411.503 411.503 ↓ 6.5 14,447 1

Seq Scan on xendra.c_invoice i (cost=0.00..48,356.94 rows=2,212 width=6) (actual time=6.942..411.503 rows=14,447 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.c_doc (...)
  • Filter: ((i.isactive = 'Y'::bpchar) AND (i.issotrx = 'Y'::bpchar) AND (i.ad_client_id = 1000038::numeric) AND (date(i.dateinvoiced) >= '2020-03-01'::date) AND (date(i.dateinvoiced) <= '2020-03-15'::date))
  • Rows Removed by Filter: 434460
  • Buffers: shared hit=18918 read=17094
7. 72.235 72.235 ↑ 3.5 2 14,447

Index Scan using c_invoiceline_invoice on xendra.c_invoiceline il (cost=0.42..8.83 rows=7 width=28) (actual time=0.004..0.005 rows=2 loops=14,447)

  • 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_id, il (...)
  • Index Cond: (il.c_invoice_id = i.c_invoice_id)
  • Filter: ((COALESCE(il.m_product_id, 0::numeric) <> 0::numeric) AND (il.isactive = 'Y'::bpchar))
  • Buffers: shared hit=58696
8.          

SubPlan (for HashAggregate)

9. 21.795 51,363.550 ↑ 1.0 1 7,265

Nested Loop (cost=4,282.88..4,290.93 rows=1 width=6) (actual time=7.069..7.070 rows=1 loops=7,265)

  • Output: ilne.priceactual
  • Buffers: shared hit=32208476 read=3945
10. 174.360 51,283.635 ↑ 1.0 1 7,265

Aggregate (cost=4,282.46..4,282.47 rows=1 width=7) (actual time=7.059..7.059 rows=1 loops=7,265)

  • Output: max(il_1.c_invoiceline_id)
  • Buffers: shared hit=32179474 read=3944
11. 1,947.020 51,109.275 ↓ 25.0 50 7,265

Nested Loop (cost=11.59..4,282.45 rows=2 width=7) (actual time=0.435..7.035 rows=50 loops=7,265)

  • Output: il_1.c_invoiceline_id
  • Join Filter: (i_1.m_pricelist_id = pl.m_pricelist_id)
  • Rows Removed by Join Filter: 645
  • Buffers: shared hit=32179474 read=3944
12. 36.325 36.325 ↑ 1.0 14 7,265

Seq Scan on xendra.m_pricelist pl (cost=0.00..1.14 rows=14 width=16) (actual time=0.002..0.005 rows=14 loops=7,265)

  • Output: pl.m_pricelist_id, pl.ad_client_id, pl.ad_org_id, pl.isactive, pl.created, pl.createdby, pl.updated, pl.updatedby, pl.name, pl.description, pl.basepricelist_id, pl.istaxincluded, pl.issopricelist, pl.is (...)
  • Buffers: shared hit=7265
13. 1,053.425 49,125.930 ↓ 25.0 50 101,710

Materialize (cost=11.59..4,280.90 rows=2 width=14) (actual time=0.031..0.483 rows=50 loops=101,710)

  • Output: il_1.c_invoiceline_id, i_1.m_pricelist_id
  • Buffers: shared hit=32172209 read=3944
14. 791.885 48,072.505 ↓ 25.0 50 7,265

Nested Loop (cost=11.59..4,280.89 rows=2 width=14) (actual time=0.426..6.617 rows=50 loops=7,265)

  • Output: il_1.c_invoiceline_id, i_1.m_pricelist_id
  • Join Filter: (il_1.c_tax_id = t.c_tax_id)
  • Rows Removed by Join Filter: 198
  • Buffers: shared hit=32172209 read=3944
15. 21.795 21.795 ↑ 1.0 5 7,265

Seq Scan on xendra.c_tax t (cost=0.00..1.05 rows=5 width=16) (actual time=0.001..0.003 rows=5 loops=7,265)

  • Output: t.c_tax_id, t.ad_client_id, t.ad_org_id, t.isactive, t.created, t.createdby, t.updated, t.name, t.updatedby, t.description, t.taxindicator, t.isdocumentlevel, t.validfrom, t.issummary, t.req (...)
  • Buffers: shared hit=7265
16. 494.020 47,258.825 ↓ 25.0 50 36,325

Materialize (cost=11.59..4,279.69 rows=2 width=21) (actual time=0.084..1.301 rows=50 loops=36,325)

  • Output: il_1.c_invoiceline_id, il_1.c_tax_id, i_1.m_pricelist_id
  • Buffers: shared hit=32164944 read=3944
17. 4,459.545 46,764.805 ↓ 25.0 50 7,265

Nested Loop (cost=11.59..4,279.68 rows=2 width=21) (actual time=0.419..6.437 rows=50 loops=7,265)

  • Output: il_1.c_invoiceline_id, il_1.c_tax_id, i_1.m_pricelist_id
  • Buffers: shared hit=32164944 read=3944
18. 8,347.485 9,873.135 ↓ 2.5 893 7,265

Bitmap Heap Scan on xendra.c_invoiceline il_1 (cost=11.17..1,351.96 rows=354 width=20) (actual time=0.350..1.359 rows=893 loops=7,265)

  • Output: il_1.c_invoiceline_id, il_1.ad_client_id, il_1.ad_org_id, il_1.isactive, il_1.created, il_1.createdby, il_1.updated, il_1.updatedby, il_1.c_invoice_id, il_1.c_orderline_id, il_1. (...)
  • Recheck Cond: (il_1.m_product_id = il.m_product_id)
  • Heap Blocks: exact=6125616
  • Buffers: shared hit=6168400 read=3944
19. 1,525.650 1,525.650 ↓ 2.5 893 7,265

Bitmap Index Scan on invoicelinetest (cost=0.00..11.08 rows=354 width=0) (actual time=0.210..0.210 rows=893 loops=7,265)

  • Index Cond: (il_1.m_product_id = il.m_product_id)
  • Buffers: shared hit=42784 read=3944
20. 32,432.125 32,432.125 ↓ 0.0 0 6,486,425

Index Scan using c_invoice_pkey on xendra.c_invoice i_1 (cost=0.42..8.26 rows=1 width=13) (actual time=0.005..0.005 rows=0 loops=6,486,425)

  • 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 (...)
  • Index Cond: (i_1.c_invoice_id = il_1.c_invoice_id)
  • Filter: ((i_1.docstatus = ANY ('{CO,CL}'::bpchar[])) AND (i_1.issotrx = 'N'::bpchar) AND (date(i_1.dateinvoiced) <= '2020-03-15'::date))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=25996544
21. 58.120 58.120 ↑ 1.0 1 7,265

Index Scan using c_invoiceline_pkey on xendra.c_invoiceline ilne (cost=0.42..8.45 rows=1 width=13) (actual time=0.007..0.008 rows=1 loops=7,265)

  • 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: (ilne.c_invoiceline_id = (max(il_1.c_invoiceline_id)))
  • Buffers: shared hit=29002 read=1
22. 21.795 45,943.860 ↑ 1.0 1 7,265

Nested Loop (cost=4,278.36..4,286.41 rows=1 width=6) (actual time=6.323..6.324 rows=1 loops=7,265)

  • Output: ilne_1.priceactual
  • Buffers: shared hit=32197891
23. 210.685 45,878.475 ↑ 1.0 1 7,265

Aggregate (cost=4,277.94..4,277.95 rows=1 width=15) (actual time=6.315..6.315 rows=1 loops=7,265)

  • Output: max(i_2.dateinvoiced), max(iln.c_invoiceline_id)
  • Buffers: shared hit=32168888
24. 3,667.660 45,667.790 ↓ 10.2 51 7,265

Nested Loop (cost=11.59..4,277.91 rows=5 width=15) (actual time=0.416..6.286 rows=51 loops=7,265)

  • Output: iln.c_invoiceline_id, i_2.dateinvoiced
  • Buffers: shared hit=32168888
25. 8,042.355 9,568.005 ↓ 2.5 893 7,265

Bitmap Heap Scan on xendra.c_invoiceline iln (cost=11.17..1,351.96 rows=354 width=13) (actual time=0.349..1.317 rows=893 loops=7,265)

  • 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)
  • Heap Blocks: exact=6125616
  • Buffers: shared hit=6172344
26. 1,525.650 1,525.650 ↓ 2.5 893 7,265

Bitmap Index Scan on invoicelinetest (cost=0.00..11.08 rows=354 width=0) (actual time=0.210..0.210 rows=893 loops=7,265)

  • Index Cond: (iln.m_product_id = il.m_product_id)
  • Buffers: shared hit=46728
27. 32,432.125 32,432.125 ↓ 0.0 0 6,486,425

Index Scan using c_invoice_pkey on xendra.c_invoice i_2 (cost=0.42..8.26 rows=1 width=14) (actual time=0.005..0.005 rows=0 loops=6,486,425)

  • 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.c_invoice_id)
  • Filter: ((i_2.dateinvoiced <= '2020-03-15 20:49:09'::timestamp without time zone) AND (i_2.issotrx = 'N'::bpchar))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=25996544
28. 43.590 43.590 ↑ 1.0 1 7,265

Index Scan using c_invoiceline_pkey on xendra.c_invoiceline ilne_1 (cost=0.42..8.45 rows=1 width=13) (actual time=0.006..0.006 rows=1 loops=7,265)

  • 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: (ilne_1.c_invoiceline_id = (max(iln.c_invoiceline_id)))
  • Buffers: shared hit=29003
29. 21.795 45,871.210 ↑ 1.0 1 7,265

Nested Loop (cost=4,278.36..4,286.41 rows=1 width=6) (actual time=6.313..6.314 rows=1 loops=7,265)

  • Output: ilne_2.priceactual
  • Buffers: shared hit=32197891
30. 210.685 45,805.825 ↑ 1.0 1 7,265

Aggregate (cost=4,277.94..4,277.95 rows=1 width=15) (actual time=6.305..6.305 rows=1 loops=7,265)

  • Output: max(i_3.dateinvoiced), max(iln_1.c_invoiceline_id)
  • Buffers: shared hit=32168888
31. 3,638.600 45,595.140 ↓ 10.2 51 7,265

Nested Loop (cost=11.59..4,277.91 rows=5 width=15) (actual time=0.417..6.276 rows=51 loops=7,265)

  • Output: iln_1.c_invoiceline_id, i_3.dateinvoiced
  • Buffers: shared hit=32168888
32. 7,998.765 9,524.415 ↓ 2.5 893 7,265

Bitmap Heap Scan on xendra.c_invoiceline iln_1 (cost=11.17..1,351.96 rows=354 width=13) (actual time=0.348..1.311 rows=893 loops=7,265)

  • 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)
  • Heap Blocks: exact=6125616
  • Buffers: shared hit=6172344
33. 1,525.650 1,525.650 ↓ 2.5 893 7,265

Bitmap Index Scan on invoicelinetest (cost=0.00..11.08 rows=354 width=0) (actual time=0.210..0.210 rows=893 loops=7,265)

  • Index Cond: (iln_1.m_product_id = il.m_product_id)
  • Buffers: shared hit=46728
34. 32,432.125 32,432.125 ↓ 0.0 0 6,486,425

Index Scan using c_invoice_pkey on xendra.c_invoice i_3 (cost=0.42..8.26 rows=1 width=14) (actual time=0.005..0.005 rows=0 loops=6,486,425)

  • 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_1.c_invoice_id)
  • Filter: ((i_3.dateinvoiced <= '2020-03-15 20:49:09'::timestamp without time zone) AND (i_3.issotrx = 'N'::bpchar))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=25996544
35. 43.590 43.590 ↑ 1.0 1 7,265

Index Scan using c_invoiceline_pkey on xendra.c_invoiceline ilne_2 (cost=0.42..8.45 rows=1 width=13) (actual time=0.006..0.006 rows=1 loops=7,265)

  • 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: (ilne_2.c_invoiceline_id = (max(iln_1.c_invoiceline_id)))
  • Buffers: shared hit=29003
36. 0.014 0.038 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=252) (actual time=0.038..0.038 rows=14 loops=1)

  • Output: o.name, o.value, o.ad_org_id
  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=1
37. 0.024 0.024 ↑ 1.0 14 1

Seq Scan on xendra.ad_org o (cost=0.00..1.14 rows=14 width=252) (actual time=0.011..0.024 rows=14 loops=1)

  • Output: o.name, o.value, o.ad_org_id
  • Buffers: shared hit=1
Planning time : 4.293 ms
Execution time : 143,857.854 ms