explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eSSh : Optimization for: plan #5gMm

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 68.506 79,386.514 ↑ 1.3 13 1

GroupAggregate (cost=55,700.55..55,710.03 rows=17 width=206) (actual time=79,318.114..79,386.514 rows=13 loops=1)

  • Group Key: og.name
2. 82.672 79,318.008 ↓ 4,439.0 75,463 1

Sort (cost=55,700.55..55,700.59 rows=17 width=206) (actual time=79,306.539..79,318.008 rows=75,463 loops=1)

  • Sort Key: og.name
  • Sort Method: quicksort Memory: 9245kB
3. 45.471 79,235.336 ↓ 4,439.0 75,463 1

Hash Join (cost=39,612.70..55,700.20 rows=17 width=206) (actual time=353.418..79,235.336 rows=75,463 loops=1)

  • Hash Cond: (inv.ad_org_id = og.ad_org_id)
4. 8,297.787 79,189.824 ↓ 4,439.0 75,463 1

Hash Join (cost=39,611.07..55,698.34 rows=17 width=74) (actual time=353.357..79,189.824 rows=75,463 loops=1)

  • Hash Cond: (invl.c_invoice_id = inv.c_invoice_id)
5. 24,986.079 70,822.262 ↓ 235.0 28,774,942 1

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

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

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

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

Merge Left Join (cost=15,876.36..18,782.47 rows=266 width=58) (actual time=207.380..593.707 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. 49.606 581.143 ↓ 116.0 30,858 1

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

9. 13.197 346.389 ↓ 116.0 30,858 1

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

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

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

11. 65.310 226.114 ↑ 1.1 49,495 1

Sort (cost=15,875.05..16,008.25 rows=53,278 width=29) (actual time=207.262..226.114 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. 78.046 160.804 ↑ 1.1 49,495 1

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

  • Hash Cond: (ppr.m_pricelist_version_id = plv.m_pricelist_version_id)
13. 80.061 80.061 ↑ 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.014..80.061 rows=377,837 loops=1)

14. 2.697 2.697 ↑ 1.0 1,418 1

Hash (cost=223.00..223.00 rows=1,418 width=15) (actual time=2.697..2.697 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.280..2.336 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.254..0.254 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.041 ↑ 1.0 1 1

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

  • Sort Key: uomc.m_product_id
  • Sort Method: quicksort Memory: 25kB
17. 0.022 0.022 ↑ 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.022 rows=1 loops=1)

18. 30.717 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.026 0.141 ↓ 1.4 14 1

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

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

Nested Loop (cost=0.14..6.96 rows=13 width=22) (actual time=0.074..0.090 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.053..0.054 rows=1 loops=1)

22. 0.021 0.021 ↑ 1.0 1 1

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

  • Filter: (m_pricelist_id = '1000002'::numeric)
  • Rows Removed by Filter: 15
23. 0.025 0.025 ↑ 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.025 rows=1 loops=1)

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

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

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

Hash (cost=1.14..1.14 rows=14 width=16) (actual time=0.025..0.025 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.013..0.018 rows=22 loops=1)

27. 45,052.680 45,052.680 ↑ 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.460 rows=932 loops=30,858)

  • Index Cond: (m_product_id = mp.m_product_id)
28. 6.917 69.775 ↓ 19.9 20,186 1

Hash (cost=23,720.16..23,720.16 rows=1,012 width=18) (actual time=69.775..69.775 rows=20,186 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1281kB
29. 10.559 62.858 ↓ 19.9 20,186 1

Hash Join (cost=5.24..23,720.16 rows=1,012 width=18) (actual time=0.171..62.858 rows=20,186 loops=1)

  • Hash Cond: (inv.c_doctype_id = dt.c_doctype_id)
30. 52.225 52.225 ↓ 1.5 20,186 1

Index Scan using invdateacct on c_invoice inv (cost=0.44..23,654.40 rows=13,558 width=20) (actual time=0.085..52.225 rows=20,186 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 (issotrx = 'Y'::bpchar) AND (ad_client_id = '1000000'::numeric) AND (ad_org_id = ANY ('{0,1000020,1000009,100153
  • Rows Removed by Filter: 176
31. 0.007 0.074 ↑ 1.0 10 1

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

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

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

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

Hash (cost=1.28..1.28 rows=28 width=154) (actual time=0.041..0.041 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
34. 0.015 0.015 ↑ 1.0 28 1

Seq Scan on ad_org og (cost=0.00..1.28 rows=28 width=154) (actual time=0.009..0.015 rows=28 loops=1)

Planning time : 8.262 ms
Execution time : 79,387.245 ms