explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Za9L

Settings
# exclusive inclusive rows x rows loops node
1. 69.930 506,928.913 ↓ 3.2 13 1

GroupAggregate (cost=2,045,247.26..2,045,249.49 rows=4 width=206) (actual time=506,859.826..506,928.913 rows=13 loops=1)

  • Group Key: og.name
2. 83.099 506,858.983 ↓ 18,865.8 75,463 1

Sort (cost=2,045,247.26..2,045,247.27 rows=4 width=206) (actual time=506,848.378..506,858.983 rows=75,463 loops=1)

  • Sort Key: og.name
  • Sort Method: quicksort Memory: 9245kB
3. 78.196 506,775.884 ↓ 18,865.8 75,463 1

Nested Loop (cost=136,652.44..2,045,247.22 rows=4 width=206) (actual time=513.086..506,775.884 rows=75,463 loops=1)

4. 3,598.441 506,546.762 ↓ 18,865.8 75,463 1

Merge Join (cost=136,652.30..2,045,246.56 rows=4 width=74) (actual time=513.062..506,546.762 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: 2336628
5. 380.947 380.995 ↑ 1.0 377,575 1

Merge Left Join (cost=1.44..21,003.01 rows=378,092 width=56) (actual time=0.138..380.995 rows=377,575 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.085..281.7
  • Filter: (ad_client_id = '1000000'::numeric)
  • Rows Removed by Filter: 86
6. 0.006 0.048 ↑ 1.0 1 1

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

7. 0.038 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
8. 0.004 0.004 ↑ 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.004 rows=1 loops=1)

9. 364.628 852.398 ↓ 1,759.4 2,412,082 1

Materialize (cost=136,438.06..136,528.52 rows=1,371 width=55) (actual time=366.536..852.398 rows=2,412,082 loops=1)

10. 76.908 487.770 ↓ 55.0 75,463 1

Merge Join (cost=136,438.06..136,525.09 rows=1,371 width=55) (actual time=366.531..487.770 rows=75,463 loops=1)

  • Merge Cond: (invl.m_product_id = mp.m_product_id)
11. 88.940 333.484 ↓ 19.4 75,464 1

Sort (cost=133,232.82..133,242.52 rows=3,880 width=32) (actual time=308.087..333.484 rows=75,464 loops=1)

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

Nested Loop (cost=5.80..133,001.54 rows=3,880 width=32) (actual time=0.269..244.544 rows=75,468 loops=1)

  • -> Index Scan using c_invoiceline_invoice on c_invoiceline invl (cost=0.56..107.09 rows=148 width=25) (actual t
13. 65.821 65.891 ↓ 20.0 20,186 1

Hash Join (cost=5.24..23,563.54 rows=1,008 width=23) (actual time=0.197..65.891 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..23497.99 rows=13513 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.070 0.070 ↑ 1.0 10 1

Hash (cost=4.67..4.67 rows=10 width=10) (actual time=0.070..0.070 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.057 rows=10 lo
  • Filter: (docbasetype = ANY ('{ARI,ARC}'::bpchar[]))
  • Rows Removed by Filter: 107
15. 36.167 77.378 ↓ 9.2 100,410 1

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

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

Hash Join (cost=8.55..2,471.29 rows=10,933 width=23) (actual time=0.215..41.211 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.032..10.471 rows=30908 loop
17. 0.008 0.165 ↓ 1.3 13 1

Hash (cost=8.43..8.43 rows=10 width=38) (actual time=0.165..0.165 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.015 0.157 ↓ 1.3 13 1

Hash Join (cost=1.46..8.43 rows=10 width=38) (actual time=0.134..0.157 rows=13 loops=1)

  • Hash Cond: (tx.c_taxcategory_id = tc.c_taxcategory_id)
19. 0.040 0.118 ↑ 1.0 13 1

Nested Loop (cost=0.14..6.96 rows=13 width=22) (actual time=0.100..0.118 rows=13 loops=1)

  • -> Seq Scan on c_tax tx (cost=0.00..1.46 rows=13 width=10) (actual time=0.015..0.027 rows=13
20. 0.078 0.078 ↑ 1.0 1 1

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

  • -> Seq Scan on m_pricelist pl (cost=0.00..1.20 rows=1 width=24) (actual time=0.037..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: 24
21. 0.024 0.024 ↓ 1.6 22 1

Hash (cost=1.14..1.14 rows=14 width=16) (actual time=0.024..0.024 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.010..0.013
22.          

SubPlan (forMerge Join)

23. 0.000 501,714.928 ↑ 1.0 1 2,412,091

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

24. 14,472.546 501,714.928 ↑ 2.0 1 2,412,091

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

  • Sort Key: plvin.validfrom DESC
  • Sort Method: quicksort Memory: 25kB
25. 487,242.382 487,242.382 ↓ 4.0 8 2,412,091

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

  • -> 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: 124035857
  • -> 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)
Planning time : 26.062 ms
Execution time : 506,931.507 ms