explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pSdq

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 89,633.398 ↓ 3.2 13 1

GroupAggregate (cost=6,378,771.56..6,378,773.79 rows=4 width=41) (actual time=89,582.738..89,633.398 rows=13 loops=1)

  • Group Key: og.name
  • Buckets: 32768 Batches: 1 Memory Usage: 1643kB
  • Sort Key: pl.c_currency_id
2. 34.096 89,578.424 ↓ 18,865.8 75,463 1

Sort (cost=6,378,771.56..6,378,771.57 rows=4 width=41) (actual time=89,574.177..89,578.424 rows=75,463 loops=1)

  • Sort Key: og.name
  • Sort Method: quicksort Memory: 9244kB
3. 1,197.218 89,544.328 ↓ 18,865.8 75,463 1

Merge Join (cost=1,808,202.57..6,378,771.52 rows=4 width=41) (actual time=89,506.043..89,544.328 rows=75,463 loops=1)

  • Merge Cond: (cr.c_currency_id = pl.c_currency_id)
4. 1,486.800 88,347.110 ↓ 13,661.4 9,508,339 1

Nested Loop (cost=1,808,201.36..8,132,485.79 rows=696 width=44) (actual time=86,098.231..88,347.110 rows=9,508,339 loops=1)

5. 0.184 0.184 ↑ 1.4 127 1

Index Scan using c_currency_pkey on c_currency cr (cost=0.14..22.69 rows=174 width=9) (actual time=0.003..0.184 rows=127 loops=1)

6. 541.309 86,860.126 ↓ 18,717.2 74,869 127

Materialize (cost=1,808,201.21..8,132,454.42 rows=4 width=35) (actual time=677.939..683.938 rows=74,869 loops=127)

7. 135.499 86,318.817 ↓ 18,865.8 75,463 1

Nested Loop (cost=1,808,201.21..8,132,454.40 rows=4 width=35) (actual time=86,098.221..86,318.817 rows=75,463 loops=1)

8. 27.767 86,183.318 ↓ 18,865.8 75,463 1

Hash Join (cost=1,808,201.07..8,132,453.74 rows=4 width=34) (actual time=86,098.206..86,183.318 rows=75,463 loops=1)

  • Hash Cond: (inv.c_doctype_id = dt.c_doctype_id)
9. 85,818.458 86,155.551 ↓ 1,540.1 75,463 1

Merge Join (cost=1,808,197.97..8,132,450.41 rows=49 width=36) (actual time=86,098.146..86,155.551 rows=75,463 loops=1)

  • Merge Cond: (mp.c_taxcategory_id = tx.c_taxcategory_id)
  • -> Nested Loop (cost=1808196.75..1639789386.81 rows=259 width=45) (actual time=86098.109..86126.105 rows=75463
  • Join Filter: (mp.c_taxcategory_id = tc.c_taxcategory_id)
  • Rows Removed by Join Filter: 981019
  • -> Index Only Scan using c_taxcategory_pkey on c_taxcategory tc (cost=0.14..12.31 rows=12 width=6) (actua
  • Heap Fetches: 14
  • -> Materialize (cost=1808196.62..1639789328.53 rows=259 width=39) (actual time=6103.875..6137.728 rows=75
  • -> Merge Join (cost=1808196.62..1639789327.23 rows=259 width=39) (actual time=85454.250..85865.791
  • Merge Cond: ((pp.m_pricelist_version_id = ((SubPlan 1))) AND (pp.m_product_id = invl.m_product_id))
10. 337.093 337.093 ↑ 1.1 376,953 1

Nested Loop Left Join (cost=0.42..100,204.26 rows=402,210 width=29) (actual time=0.042..337.093 rows=376,953 loops=1)

  • Join Filter: (uomc.m_product_id = pp.m_product_id)
  • Rows Removed by Join Filter: 376953
11. 0.000 150.627 ↑ 1.1 376,953 1

Index Scan using m_productprice_pkey on m_productprice pp (cost=0.42..94,170.09 rows=402,210 width=24) (actual time=0.034..150.627 rows=376,953 loops=1)

  • Filter: (ad_client_id = '1000000'::numeric)
  • Rows Removed by Filter: 86
12. 0.000 0.000 ↑ 1.0 1 376,953

Materialize (cost=0.00..1.01 rows=1 width=10) (actual time=0.000..0.000 rows=1 loops=376,953)

13. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on c_uom_conversion uomc (cost=0.00..1.01 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=1)

14. 144.505 85,445.858 ↓ 1.2 75,463 1

Sort (cost=1,808,196.19..1,808,349.68 rows=61,393 width=47) (actual time=85,436.655..85,445.858 rows=75,463 loops=1)

  • Sort Key: ((SubPlan 1)), invl.m_product_id
  • Sort Method: quicksort Memory: 13684kB
15. 61.175 85,301.353 ↓ 1.2 75,463 1

Hash Join (cost=596,149.54..1,803,313.67 rows=61,393 width=47) (actual time=16,096.939..85,301.353 rows=75,463 loops=1)

  • Hash Cond: (invl.m_product_id = mp.m_product_id)
16. 5,555.401 17,152.686 ↓ 1.2 75,468 1

Hash Join (cost=593,887.72..1,800,207.10 rows=61,553 width=34) (actual time=16,076.459..17,152.686 rows=75,468 loops=1)

  • Hash Cond: (invl.c_invoice_id = inv.c_invoice_id)
17. 3,178.881 3,178.881 ↓ 1.0 28,698,406 1

Seq Scan on c_invoiceline invl (cost=0.00..1,098,115.98 rows=28,690,098 width=25) (actual time=0.051..3,178.881 rows=28,698,406 loops=1)

18. 5.857 8,418.404 ↓ 1.3 20,186 1

Hash (cost=593,687.78..593,687.78 rows=15,996 width=25) (actual time=8,418.404..8,418.404 rows=20,186 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1439kB
19. 8,412.547 8,412.547 ↓ 1.3 20,186 1

Seq Scan on c_invoice inv (cost=0.00..593,687.78 rows=15,996 width=25) (actual time=5,547.076..8,412.547 rows=20,186 loops=1)

  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ad_client_id = '1000000'::numeric) AND (issotrx = 'Y'::bpchar) AND (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-MM-DD HH24:MI:SS'::text)) AND (ad_org_id = ANY ('{0,1000020,1000009,10015300,1000006,1000015,1000014,11893001,1000012,1000013,1000005,11893000,1000004,1000000}'::numeric[])))
  • Rows Removed by Filter: 7436094
20. 5.618 19.866 ↓ 1.0 30,859 1

Hash (cost=1,893.03..1,893.03 rows=29,503 width=13) (actual time=19.866..19.866 rows=30,859 loops=1)

21. 14.248 14.248 ↓ 1.0 30,859 1

Seq Scan on m_product mp (cost=0.00..1,893.03 rows=29,503 width=13) (actual time=0.006..14.248 rows=30,859 loops=1)

22.          

SubPlan (forHash Join)

23. 75.463 68,067.626 ↑ 1.0 1 75,463

Limit (cost=0.71..3,532.93 rows=1 width=15) (actual time=0.901..0.902 rows=1 loops=75,463)

24. 12,339.522 67,992.163 ↑ 3.0 1 75,463

Nested Loop (cost=0.71..10,597.38 rows=3 width=15) (actual time=0.901..0.901 rows=1 loops=75,463)

25. 8,829.171 8,829.171 ↑ 4.5 310 75,463

Index Scan using m_pricelist_version_validfrom on m_pricelist_version plvin (cost=0.29..752.72 rows=1,407 width=15) (actual time=0.013..0.117 rows=310 loops=75,463)

  • Index Cond: (m_pricelist_id = '1000002'::numeric)
  • Filter: (isactive = 'Y'::bpchar)
26. 46,823.470 46,823.470 ↓ 0.0 0 23,411,735

Index Only Scan using m_productprice_pkey on m_productprice ppr (cost=0.42..6.99 rows=1 width=6) (actual time=0.002..0.002 rows=0 loops=23,411,735)

  • Index Cond: ((m_pricelist_version_id = plvin.m_pricelist_version_id) AND (m_product_id = invl.m_product_id))
  • Heap Fetches: 75463
27. 5.373 5.392 ↓ 1.7 5 1

Sort (cost=1.22..1.23 rows=3 width=9) (actual time=0.033..5.392 rows=5 loops=1)

  • Sort Key: tx.c_taxcategory_id
  • Sort Method: quicksort Memory: 25kB
28. 0.019 0.019 ↓ 1.7 5 1

Seq Scan on c_tax tx (cost=0.00..1.20 rows=3 width=9) (actual time=0.015..0.019 rows=5 loops=1)

  • Filter: (isdefault = 'Y'::bpchar)
  • Rows Removed by Filter: 13
29. 0.012 0.042 ↑ 1.0 6 1

Hash (cost=3.03..3.03 rows=6 width=10) (actual time=0.042..0.042 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.030 0.030 ↑ 1.0 6 1

Seq Scan on c_doctype dt (cost=0.00..3.03 rows=6 width=10) (actual time=0.012..0.030 rows=6 loops=1)

  • Filter: (docbasetype = ANY ('{ARI,ARC}'::bpchar[]))
  • Rows Removed by Filter: 76
31. 75.463 75.463 ↑ 1.0 1 75,463

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

  • Index Cond: (ad_org_id = inv.ad_org_id)
32. 3.458 3.468 ↑ 1.0 1 1

Materialize (cost=1.21..1.22 rows=1 width=7) (actual time=0.012..3.468 rows=1 loops=1)

33. 0.003 0.010 ↑ 1.0 1 1

Sort (cost=1.21..1.21 rows=1 width=7) (actual time=0.010..0.010 rows=1 loops=1)

  • Sort Method: quicksort Memory: 25kB
34. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on m_pricelist pl (cost=0.00..1.20 rows=1 width=7) (actual time=0.007..0.007 rows=1 loops=1)

  • Filter: (m_pricelist_id = '1000002'::numeric)
  • Rows Removed by Filter: 15