explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5gMm

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5.966 73,128.332 ↑ 2.0 2 1

GroupAggregate (cost=55,364.08..55,366.31 rows=4 width=206) (actual time=73,128.330..73,128.332 rows=2 loops=1)

  • Group Key: og.name
2. 9.116 73,122.366 ↓ 1,684.8 6,739 1

Sort (cost=55,364.08..55,364.09 rows=4 width=206) (actual time=73,121.831..73,122.366 rows=6,739 loops=1)

  • Sort Key: og.name
  • Sort Method: quicksort Memory: 719kB
3. 9.372 73,113.250 ↓ 1,684.8 6,739 1

Nested Loop (cost=39,276.37..55,364.04 rows=4 width=206) (actual time=320.617..73,113.250 rows=6,739 loops=1)

4. 5,211.990 73,083.661 ↓ 1,684.8 6,739 1

Hash Join (cost=39,276.23..55,363.37 rows=4 width=74) (actual time=320.595..73,083.661 rows=6,739 loops=1)

  • Hash Cond: (invl.c_invoice_id = inv.c_invoice_id)
5. 21,806.385 67,815.174 ↓ 235.0 28,774,942 1

Nested Loop (cost=15,878.26..31,506.25 rows=122,430 width=72) (actual time=205.473..67,815.174 rows=28,774,942 loops=1)

  • Join Filter: (plvpi.m_product_id = invl.m_product_id)
6. 118.843 740.103 ↓ 328.3 30,858 1

Nested Loop (cost=15,877.82..18,830.82 rows=94 width=68) (actual time=205.382..740.103 rows=30,858 loops=1)

  • Join Filter: (tc.c_taxcategory_id = mp.c_taxcategory_id)
  • Rows Removed by Join Filter: 401154
7. 12.075 590.402 ↓ 116.0 30,858 1

Merge Left Join (cost=15,876.36..18,782.47 rows=266 width=58) (actual time=204.700..590.402 rows=30,858 loops=1)

  • Merge Cond: (plvpi.m_product_id = uomc.m_product_id)
  • Join Filter: (uomc.c_uom_to_id = plvpi.c_uom_id)
8. 48.515 578.285 ↓ 116.0 30,858 1

Nested Loop (cost=15,875.34..18,780.77 rows=266 width=31) (actual time=204.652..578.285 rows=30,858 loops=1)

9. 12.594 344.622 ↓ 116.0 30,858 1

Subquery Scan on plvpi (cost=15,875.05..17,872.98 rows=266 width=18) (actual time=204.614..344.622 rows=30,858 loops=1)

  • Filter: (plvpi.rank = 1)
  • Rows Removed by Filter: 18637
10. 108.167 332.028 ↑ 1.1 49,495 1

WindowAgg (cost=15,875.05..17,207.00 rows=53,278 width=29) (actual time=204.606..332.028 rows=49,495 loops=1)

11. 65.688 223.861 ↑ 1.1 49,495 1

Sort (cost=15,875.05..16,008.25 rows=53,278 width=29) (actual time=204.591..223.861 rows=49,495 loops=1)

  • Sort Key: ppr.m_product_id, ppr.m_attributesetinstance_id, ppr.c_uom_id, plv.validfrom DESC
  • Sort Method: quicksort Memory: 5403kB
12. 77.512 158.173 ↑ 1.1 49,495 1

Hash Join (cost=240.72..11,692.39 rows=53,278 width=29) (actual time=2.694..158.173 rows=49,495 loops=1)

  • Hash Cond: (ppr.m_pricelist_version_id = plv.m_pricelist_version_id)
13. 78.014 78.014 ↑ 1.0 377,837 1

Seq Scan on m_productprice ppr (cost=0.00..9,499.92 rows=378,392 width=27) (actual time=0.017..78.014 rows=377,837 loops=1)

14. 2.647 2.647 ↑ 1.0 1,418 1

Hash (cost=223.00..223.00 rows=1,418 width=15) (actual time=2.647..2.647 rows=1,418 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
  • -> Bitmap Heap Scan on m_pricelist_version plv (cost=23.27..223.00 rows=1418 width=15) (actual time=0.292..2.275 rows=1418 loop
  • Recheck Cond: (m_pricelist_id = '1000002'::numeric)
  • Heap Blocks: exact=180
  • -> Bitmap Index Scan on m_pricelist_version_validfrom (cost=0.00..22.92 rows=1418 width=0) (actual time=0.268..0.268 rows
  • Index Cond: (m_pricelist_id = '1000002'::numeric)
15. 185.148 185.148 ↑ 1.0 1 30,858

Index Scan using m_product_pkey on m_product mp (cost=0.29..3.40 rows=1 width=13) (actual time=0.005..0.006 rows=1 loops=30,858)

  • Index Cond: (m_product_id = plvpi.m_product_id)
16. 0.019 0.042 ↑ 1.0 1 1

Sort (cost=1.02..1.02 rows=1 width=64) (actual time=0.042..0.042 rows=1 loops=1)

  • Sort Key: uomc.m_product_id
  • Sort Method: quicksort Memory: 25kB
17. 0.023 0.023 ↑ 1.0 1 1

Seq Scan on c_uom_conversion uomc (cost=0.00..1.01 rows=1 width=64) (actual time=0.022..0.023 rows=1 loops=1)

18. 30.169 30.858 ↓ 1.4 14 30,858

Materialize (cost=1.46..8.48 rows=10 width=38) (actual time=0.000..0.001 rows=14 loops=30,858)

19. 0.030 0.689 ↓ 1.4 14 1

Hash Join (cost=1.46..8.43 rows=10 width=38) (actual time=0.664..0.689 rows=14 loops=1)

  • Hash Cond: (tx.c_taxcategory_id = tc.c_taxcategory_id)
20. 0.006 0.624 ↓ 1.1 14 1

Nested Loop (cost=0.14..6.96 rows=13 width=22) (actual time=0.610..0.624 rows=14 loops=1)

21. 0.008 0.054 ↑ 1.0 1 1

Nested Loop (cost=0.14..5.37 rows=1 width=12) (actual time=0.052..0.054 rows=1 loops=1)

22. 0.020 0.020 ↑ 1.0 1 1

Seq Scan on m_pricelist pl (cost=0.00..1.20 rows=1 width=24) (actual time=0.019..0.020 rows=1 loops=1)

  • Filter: (m_pricelist_id = '1000002'::numeric)
  • Rows Removed by Filter: 15
23. 0.026 0.026 ↑ 1.0 1 1

Index Scan using c_currency_pkey on c_currency cr (cost=0.14..4.16 rows=1 width=9) (actual time=0.025..0.026 rows=1 loops=1)

  • Index Cond: (c_currency_id = pl.c_currency_id)
24. 0.564 0.564 ↓ 1.1 14 1

Seq Scan on c_tax tx (cost=0.00..1.46 rows=13 width=10) (actual time=0.553..0.564 rows=14 loops=1)

  • Filter: (isdefault = 'Y'::bpchar)
  • Rows Removed by Filter: 23
25. 0.017 0.035 ↓ 1.6 22 1

Hash (cost=1.14..1.14 rows=14 width=16) (actual time=0.035..0.035 rows=22 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.018 0.018 ↓ 1.6 22 1

Seq Scan on c_taxcategory tc (cost=0.00..1.14 rows=14 width=16) (actual time=0.016..0.018 rows=22 loops=1)

27. 45,268.686 45,268.686 ↑ 2.7 932 30,858

Index Scan using c_invoiceline_product on c_invoiceline invl (cost=0.44..102.99 rows=2,548 width=25) (actual time=0.013..1.467 rows=932 loops=30,858)

  • Index Cond: (m_product_id = mp.m_product_id)
28. 0.919 56.497 ↓ 9.3 2,028 1

Hash (cost=23,395.25..23,395.25 rows=218 width=18) (actual time=56.497..56.497 rows=2,028 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 119kB
29. 1.261 55.578 ↓ 9.3 2,028 1

Hash Join (cost=5.24..23,395.25 rows=218 width=18) (actual time=12.627..55.578 rows=2,028 loops=1)

  • Hash Cond: (inv.c_doctype_id = dt.c_doctype_id)
30. 54.216 54.216 ↑ 1.4 2,028 1

Index Scan using invdateacct on c_invoice inv (cost=0.44..23,377.33 rows=2,916 width=20) (actual time=12.493..54.216 rows=2,028 loops=1)

  • Index Cond: ((dateacct >= to_timestamp('2018-04-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (dateacct <= to_timestamp('2018-04-08 23:59:59'::text, 'YYYY-
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ad_org_id = ANY ('{1000000,1000004}'::numeric[])) AND (issotrx = 'Y'::bpchar) AND (ad_client_id = '1000000'::nu
  • Rows Removed by Filter: 18334
31. 0.011 0.101 ↑ 1.0 10 1

Hash (cost=4.67..4.67 rows=10 width=10) (actual time=0.101..0.101 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.090 0.090 ↑ 1.0 10 1

Seq Scan on c_doctype dt (cost=0.00..4.67 rows=10 width=10) (actual time=0.026..0.090 rows=10 loops=1)

  • Filter: (docbasetype = ANY ('{ARI,ARC}'::bpchar[]))
  • Rows Removed by Filter: 107
33. 20.217 20.217 ↑ 1.0 1 6,739

Index Scan using ad_org_pkey on ad_org og (cost=0.14..0.16 rows=1 width=154) (actual time=0.003..0.003 rows=1 loops=6,739)

  • Index Cond: (ad_org_id = inv.ad_org_id)