explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8rO5

Settings
# exclusive inclusive rows x rows loops node
1. 67.160 498,174.747 ↓ 3.2 13 1

GroupAggregate (cost=2,029,532.88..2,029,535.11 rows=4 width=206) (actual time=498,108.001..498,174.747 rows=13 loops=1)

  • Group Key: og.name
2. 83.517 498,107.587 ↓ 18,865.8 75,463 1

Sort (cost=2,029,532.88..2,029,532.89 rows=4 width=206) (actual time=498,097.103..498,107.587 rows=75,463 loops=1)

  • Sort Key: og.name
  • Sort Method: quicksort Memory: 9245kB
3. 76.615 498,024.070 ↓ 18,865.8 75,463 1

Nested Loop (cost=136,303.48..2,029,532.84 rows=4 width=206) (actual time=498.496..498,024.070 rows=75,463 loops=1)

4. 2,108.890 497,796.529 ↓ 18,865.8 75,463 1

Merge Join (cost=136,303.34..2,029,532.18 rows=4 width=74) (actual time=498.483..497,796.529 rows=75,463 loops=1)

  • Merge Cond: (pp.m_product_id = invl.m_product_id)
  • Join Filter: ((SubPlan 1) = pp.m_pricelist_version_id)
  • Rows Removed by Join Filter: 2336697
5. 365.451 365.479 ↑ 1.0 377,596 1

Merge Left Join (cost=1.44..21,003.01 rows=378,092 width=56) (actual time=0.101..365.479 rows=377,596 loops=1)

  • Merge Cond: (pp.m_product_id = uomc.m_product_id)
  • Join Filter: (uomc.c_uom_to_id = pp.c_uom_id)
  • -> Index Scan using m_productprice_pkey on m_productprice pp (cost=0.42..20056.51 rows=378092 width=29) (actual time=0.067..267.2
  • Filter: (ad_client_id = '1000000'::numeric)
  • Rows Removed by Filter: 86
6. 0.012 0.028 ↑ 1.0 1 1

Materialize (cost=1.02..1.03 rows=1 width=64) (actual time=0.028..0.028 rows=1 loops=1)

7. 0.011 0.016 ↑ 1.0 1 1

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

  • Sort Key: uomc.m_product_id
  • Sort Method: quicksort Memory: 25kB
8. 0.005 0.005 ↑ 1.0 1 1

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

9. 357.176 829.360 ↓ 1,763.3 2,412,151 1

Materialize (cost=136,089.10..136,179.48 rows=1,368 width=55) (actual time=353.835..829.360 rows=2,412,151 loops=1)

10. 74.232 472.184 ↓ 55.2 75,463 1

Merge Join (cost=136,089.10..136,176.06 rows=1,368 width=55) (actual time=353.832..472.184 rows=75,463 loops=1)

  • Merge Cond: (invl.m_product_id = mp.m_product_id)
11. 97.187 327.817 ↓ 19.5 75,464 1

Sort (cost=132,883.86..132,893.54 rows=3,870 width=32) (actual time=301.800..327.817 rows=75,464 loops=1)

  • Sort Key: invl.m_product_id
  • Sort Method: quicksort Memory: 8968kB
12. 171.975 230.630 ↓ 19.5 75,468 1

Nested Loop (cost=5.80..132,653.25 rows=3,870 width=32) (actual time=0.148..230.630 rows=75,468 loops=1)

  • -> Index Scan using c_invoiceline_invoice on c_invoiceline invl (cost=0.56..106.72 rows=151 width=25) (actual t
13. 58.586 58.655 ↓ 20.1 20,186 1

Hash Join (cost=5.24..23,768.86 rows=1,006 width=23) (actual time=0.131..58.655 rows=20,186 loops=1)

  • Hash Cond: (inv.c_doctype_id = dt.c_doctype_id)
  • -> Index Scan using invdateacct on c_invoice inv (cost=0.44..23703.45 rows=13480 width=25) (actual time=0
  • Index Cond: ((dateacct >= to_timestamp('2018-04-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) A
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ad_client_id = '1000000'::numeric) AND (issotrx
  • Rows Removed by Filter: 176
  • Index Cond: (c_invoice_id = inv.c_invoice_id)
14. 0.069 0.069 ↑ 1.0 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Seq Scan on c_doctype dt (cost=0.00..4.67 rows=10 width=10) (actual time=0.014..0.059 rows=10 lo
  • Filter: (docbasetype = ANY ('{ARI,ARC}'::bpchar[]))
  • Rows Removed by Filter: 107
15. 33.892 70.135 ↓ 9.2 100,410 1

Sort (cost=3,204.69..3,232.03 rows=10,933 width=23) (actual time=51.989..70.135 rows=100,410 loops=1)

  • Sort Key: mp.m_product_id
  • Sort Method: quicksort Memory: 3182kB
16. 36.121 36.243 ↓ 2.8 30,908 1

Hash Join (cost=8.55..2,471.29 rows=10,933 width=23) (actual time=0.155..36.243 rows=30,908 loops=1)

  • Hash Cond: (mp.c_taxcategory_id = tc.c_taxcategory_id)
  • -> Seq Scan on m_product mp (cost=0.00..2126.59 rows=30859 width=13) (actual time=0.014..5.746 rows=30908 loops
17. 0.010 0.122 ↓ 1.4 14 1

Hash (cost=8.43..8.43 rows=10 width=38) (actual time=0.122..0.122 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.016 0.112 ↓ 1.4 14 1

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

  • Hash Cond: (tx.c_taxcategory_id = tc.c_taxcategory_id)
19. 0.036 0.078 ↓ 1.1 14 1

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

  • -> Seq Scan on c_tax tx (cost=0.00..1.46 rows=13 width=10) (actual time=0.011..0.025 rows=14
20. 0.042 0.042 ↑ 1.0 1 1

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

  • -> Seq Scan on m_pricelist pl (cost=0.00..1.20 rows=1 width=24) (actual time=0.016..0.0
  • Filter: (m_pricelist_id = '1000002'::numeric)
  • Rows Removed by Filter: 15
  • -> Index Scan using c_currency_pkey on c_currency cr (cost=0.14..4.16 rows=1 width=9) (
  • Index Cond: (c_currency_id = pl.c_currency_id)
  • Filter: (isdefault = 'Y'::bpchar)
  • Rows Removed by Filter: 23
21. 0.018 0.018 ↓ 1.6 22 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Seq Scan on c_taxcategory tc (cost=0.00..1.14 rows=14 width=16) (actual time=0.005..0.008
22.          

SubPlan (forMerge Join)

23. 2,412.160 494,492.800 ↑ 1.0 1 2,412,160

Limit (cost=79.18..79.19 rows=1 width=15) (actual time=0.205..0.205 rows=1 loops=2,412,160)

24. 12,060.800 492,080.640 ↑ 2.0 1 2,412,160

Sort (cost=79.18..79.19 rows=2 width=15) (actual time=0.204..0.204 rows=1 loops=2,412,160)

  • Sort Key: plvin.validfrom DESC
  • Sort Method: quicksort Memory: 25kB
25. 480,019.840 480,019.840 ↓ 4.0 8 2,412,160

Nested Loop (cost=0.71..79.17 rows=2 width=15) (actual time=0.015..0.199 rows=8 loops=2,412,160)

  • -> Index Only Scan using m_productprice_pkey on m_productprice ppr (cost=0.42..22.73 rows=14 width=6) (actual time=
  • Index Cond: (m_product_id = invl.m_product_id)
  • Heap Fetches: 124038500
  • -> Index Scan using m_pricelist_version_pkey on m_pricelist_version plvin (cost=0.29..4.02 rows=1 width=15) (actual
  • Index Cond: (m_pricelist_version_id = ppr.m_pricelist_version_id)
  • Filter: ((isactive = 'Y'::bpchar) AND (m_pricelist_id = '1000002'::numeric))
  • Rows Removed by Filter: 1
26. 150.926 150.926 ↑ 1.0 1 75,463

Index Scan using ad_org_pkey on ad_org og (cost=0.14..0.16 rows=1 width=154) (actual time=0.002..0.002 rows=1 loops=75,463)

  • Index Cond: (ad_org_id = inv.ad_org_id)