explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9vzl

Settings
# exclusive inclusive rows x rows loops node
1. 108.400 751,428.571 ↓ 3.2 13 1

GroupAggregate (cost=4,051,600.59..4,051,602.82 rows=4 width=206) (actual time=751,328.468..751,428.571 rows=13 loops=1)

  • Group Key: og.name
2. 106.984 751,320.171 ↓ 18,865.8 75,463 1

Sort (cost=4,051,600.59..4,051,600.60 rows=4 width=206) (actual time=751,311.424..751,320.171 rows=75,463 loops=1)

  • Sort Key: og.name
  • Sort Method: quicksort Memory: 9245kB
3. 99.183 751,213.187 ↓ 18,865.8 75,463 1

Nested Loop (cost=283,112.21..4,051,600.55 rows=4 width=206) (actual time=4,449.627..751,213.187 rows=75,463 loops=1)

4. 5,172.402 750,887.615 ↓ 18,865.8 75,463 1

Merge Join (cost=283,112.08..4,051,599.88 rows=4 width=74) (actual time=4,434.669..750,887.615 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. 120.420 1,450.068 ↑ 1.1 377,596 1

Merge Left Join (cost=48,523.83..51,538.99 rows=402,010 width=56) (actual time=1,157.499..1,450.068 rows=377,596 loops=1)

  • Merge Cond: ((pp.m_product_id = uomc.m_product_id) AND (pp.c_uom_id = uomc.c_uom_to_id))
6. 1,329.593 1,329.593 ↑ 1.1 377,596 1

Sort (cost=48,522.81..49,527.83 rows=402,010 width=29) (actual time=1,157.432..1,329.593 rows=377,596 loops=1)

  • Sort Key: pp.m_product_id, pp.c_uom_id
  • Sort Method: external merge Disk: 14984kB
  • -> Seq Scan on m_productprice pp (cost=0.00..11101.96 rows=402010 width=29) (actual time=0.017..233.755 rows=377645 loops=1
  • Filter: (ad_client_id = '1000000'::numeric)
  • Rows Removed by Filter: 86
7. 0.009 0.055 ↑ 1.0 1 1

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

8. 0.028 0.046 ↑ 1.0 1 1

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

  • Sort Key: uomc.m_product_id, uomc.c_uom_to_id
  • Sort Method: quicksort Memory: 25kB
9. 0.018 0.018 ↑ 1.0 1 1

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

10. 612.618 3,732.025 ↓ 2,054.6 2,412,151 1

Materialize (cost=234,549.31..234,632.80 rows=1,174 width=55) (actual time=2,973.953..3,732.025 rows=2,412,151 loops=1)

11. 110.635 3,119.407 ↓ 64.3 75,463 1

Merge Join (cost=234,549.31..234,629.86 rows=1,174 width=55) (actual time=2,973.932..3,119.407 rows=75,463 loops=1)

  • Merge Cond: (invl.m_product_id = mp.m_product_id)
12. 112.082 2,916.504 ↓ 21.6 75,464 1

Sort (cost=231,390.06..231,398.81 rows=3,499 width=32) (actual time=2,896.777..2,916.504 rows=75,464 loops=1)

  • Sort Key: invl.m_product_id
  • Sort Method: quicksort Memory: 8968kB
13. 1,669.021 2,804.422 ↓ 21.6 75,468 1

Nested Loop (cost=5.20..231,184.09 rows=3,499 width=32) (actual time=87.477..2,804.422 rows=75,468 loops=1)

  • -> Index Scan using c_invoiceline_invoice on c_invoiceline invl (cost=0.56..199.18 rows=151 width=25) (actual t
14. 1,135.263 1,135.401 ↓ 22.1 20,186 1

Hash Join (cost=4.64..47,552.28 rows=915 width=23) (actual time=61.411..1,135.401 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..47487.45 rows=13728 width=25) (actual time=6
  • 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)
15. 0.138 0.138 ↓ 1.7 10 1

Hash (cost=4.12..4.12 rows=6 width=10) (actual time=0.138..0.138 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Seq Scan on c_doctype dt (cost=0.00..4.12 rows=6 width=10) (actual time=0.022..0.110 rows=10 loo
  • Filter: (docbasetype = ANY ('{ARI,ARC}'::bpchar[]))
  • Rows Removed by Filter: 107
16. 39.828 92.268 ↓ 9.7 100,410 1

Sort (cost=3,158.73..3,184.67 rows=10,375 width=23) (actual time=77.106..92.268 rows=100,410 loops=1)

  • Sort Key: mp.m_product_id
  • Sort Method: quicksort Memory: 3182kB
17. 52.194 52.440 ↓ 3.0 30,908 1

Hash Join (cost=9.73..2,466.68 rows=10,375 width=23) (actual time=0.322..52.440 rows=30,908 loops=1)

  • Hash Cond: (mp.c_taxcategory_id = tc.c_taxcategory_id)
  • -> Seq Scan on m_product mp (cost=0.00..2127.59 rows=30859 width=13) (actual time=0.050..12.828 rows=30908 loop
18. 0.034 0.246 ↓ 1.4 13 1

Hash (cost=9.61..9.61 rows=9 width=39) (actual time=0.246..0.246 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.017 0.212 ↓ 1.4 13 1

Hash Join (cost=2.50..9.61 rows=9 width=39) (actual time=0.182..0.212 rows=13 loops=1)

  • Hash Cond: (tx.c_taxcategory_id = tc.c_taxcategory_id)
20. 0.048 0.169 ↓ 1.1 13 1

Nested Loop (cost=1.21..8.19 rows=12 width=23) (actual time=0.147..0.169 rows=13 loops=1)

  • -> Seq Scan on c_tax tx (cost=0.00..1.45 rows=12 width=11) (actual time=0.013..0.034 rows=13
21. 0.094 0.121 ↑ 1.0 1 1

Hash Join (cost=1.21..6.62 rows=1 width=12) (actual time=0.120..0.121 rows=1 loops=1)

  • Hash Cond: (cr.c_currency_id = pl.c_currency_id)
  • -> Seq Scan on c_currency cr (cost=0.00..4.74 rows=174 width=9) (actual time=0.016..0.0
  • Filter: (isdefault = 'Y'::bpchar)
  • Rows Removed by Filter: 23
22. 0.027 0.027 ↑ 1.0 1 1

Hash (cost=1.20..1.20 rows=1 width=24) (actual time=0.027..0.027 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Seq Scan on m_pricelist pl (cost=0.00..1.20 rows=1 width=24) (actual time=0.02
  • Filter: (m_pricelist_id = '1000002'::numeric)
  • Rows Removed by Filter: 15
23. 0.026 0.026 ↓ 1.6 21 1

Hash (cost=1.13..1.13 rows=13 width=16) (actual time=0.026..0.026 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Seq Scan on c_taxcategory tc (cost=0.00..1.13 rows=13 width=16) (actual time=0.006..0.011
24.          

SubPlan (forMerge Join)

25. 0.000 740,533.120 ↑ 1.0 1 2,412,160

Limit (cost=171.82..171.83 rows=1 width=15) (actual time=0.307..0.307 rows=1 loops=2,412,160)

26. 19,297.280 740,533.120 ↑ 2.0 1 2,412,160

Sort (cost=171.82..171.83 rows=2 width=15) (actual time=0.307..0.307 rows=1 loops=2,412,160)

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

Nested Loop (cost=0.71..171.81 rows=2 width=15) (actual time=0.023..0.299 rows=8 loops=2,412,160)

  • -> Index Only Scan using m_productprice_pkey on m_productprice ppr (cost=0.42..50.73 rows=16 width=6) (actual time=
  • Index Cond: (m_product_id = invl.m_product_id)
  • Heap Fetches: 124045273
  • -> Index Scan using m_pricelist_version_pkey on m_pricelist_version plvin (cost=0.29..7.56 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
28. 226.389 226.389 ↑ 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.003..0.003 rows=1 loops=75,463)

  • Index Cond: (ad_org_id = inv.ad_org_id)