explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WbPN

Settings
# exclusive inclusive rows x rows loops node
1. 0.048 489,691.389 ↑ 2.2 13 1

Sort (cost=1,767,999.97..1,768,000.04 rows=28 width=206) (actual time=489,691.388..489,691.389 rows=13 loops=1)

  • Sort Key: og.name
  • Sort Method: quicksort Memory: 25kB
2. 554.407 489,691.341 ↑ 2.2 13 1

HashAggregate (cost=1,767,998.94..1,767,999.29 rows=28 width=206) (actual time=489,691.335..489,691.341 rows=13 loops=1)

  • Group Key: og.name
3. 226.978 489,136.934 ↓ 2,960.7 349,361 1

Hash Join (cost=23,785.25..1,767,934.93 rows=118 width=206) (actual time=81.236..489,136.934 rows=349,361 loops=1)

  • Hash Cond: (inv.ad_org_id = og.ad_org_id)
4. 194.870 488,909.918 ↓ 2,960.7 349,361 1

Hash Join (cost=23,783.62..1,767,931.68 rows=118 width=74) (actual time=81.179..488,909.918 rows=349,361 loops=1)

  • Hash Cond: (mp.c_taxcategory_id = tc.c_taxcategory_id)
5. 33,850.149 488,715.031 ↓ 2,960.7 349,361 1

Hash Join (cost=23,782.31..1,767,928.74 rows=118 width=86) (actual time=81.132..488,715.031 rows=349,361 loops=1)

  • Hash Cond: (invl.c_invoice_id = inv.c_invoice_id)
6. 90,209.053 454,809.955 ↓ 132.3 116,721,244 1

Nested Loop (cost=0.87..1,740,836.45 rows=882,580 width=89) (actual time=0.268..454,809.955 rows=116,721,244 loops=1)

  • Join Filter: (pp.m_product_id = invl.m_product_id)
7. 224.438 4,772.252 ↓ 73.9 49,495 1

Nested Loop (cost=0.43..1,650,912.81 rows=670 width=85) (actual time=0.240..4,772.252 rows=49,495 loops=1)

  • Join Filter: (mp.c_taxcategory_id = tx.c_taxcategory_id)
  • Rows Removed by Join Filter: 643435
8. 108.922 4,498.319 ↓ 26.2 49,495 1

Nested Loop (cost=0.29..1,650,537.07 rows=1,891 width=63) (actual time=0.194..4,498.319 rows=49,495 loops=1)

9. 85.170 3,350.002 ↓ 26.2 49,495 1

Nested Loop Left Join (cost=0.00..1,647,025.41 rows=1,891 width=50) (actual time=0.180..3,350.002 rows=49,495 loops=1)

  • Join Filter: ((uomc.m_product_id = pp.m_product_id) AND (uomc.c_uom_to_id = pp.c_uom_id))
  • Rows Removed by Join Filter: 49495
10. 620.575 3,264.832 ↓ 26.2 49,495 1

Seq Scan on m_productprice pp (cost=0.00..1,646,991.30 rows=1,891 width=23) (actual time=0.167..3,264.832 rows=49,495 loops=1)

  • Filter: ((ad_client_id = '1000000'::numeric) AND (m_pricelist_version_id = (SubPlan 1)))
  • Rows Removed by Filter: 328342
11.          

SubPlan (forSeq Scan)

12. 0.000 2,644.257 ↓ 0.0 0 377,751

Limit (cost=4.32..4.32 rows=1 width=15) (actual time=0.007..0.007 rows=0 loops=377,751)

13. 2,644.257 2,644.257 ↓ 0.0 0 377,751

Sort (cost=4.32..4.32 rows=1 width=15) (actual time=0.007..0.007 rows=0 loops=377,751)

  • Sort Key: plvin.validfrom DESC
  • Sort Method: quicksort Memory: 25kB
  • -> Index Scan using m_pricelist_version_pkey on m_pricelist_version plvin (cost=0.29..4.31 rows=1 width=15) (actual time=0.004..0.0
  • Index Cond: (m_pricelist_version_id = pp.m_pricelist_version_id)
  • Filter: ((isactive = 'Y'::bpchar) AND (m_pricelist_id = '1000002'::numeric))
  • Rows Removed by Filter: 1
14. 0.000 0.000 ↑ 1.0 1 49,495

Materialize (cost=0.00..1.01 rows=1 width=64) (actual time=0.000..0.000 rows=1 loops=49,495)

15. 0.003 0.003 ↑ 1.0 1 1

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

16. 1,039.395 1,039.395 ↑ 1.0 1 49,495

Index Scan using m_product_pkey on m_product mp (cost=0.29..1.85 rows=1 width=13) (actual time=0.021..0.021 rows=1 loops=49,495)

  • Index Cond: (m_product_id = pp.m_product_id)
17. 49.437 49.495 ↓ 1.1 14 49,495

Materialize (cost=0.14..7.03 rows=13 width=22) (actual time=0.000..0.001 rows=14 loops=49,495)

18. 0.007 0.058 ↓ 1.1 14 1

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

19. 0.004 0.023 ↑ 1.0 1 1

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

20. 0.011 0.011 ↑ 1.0 1 1

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

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

  • Index Cond: (c_currency_id = pl.c_currency_id)
22. 0.028 0.028 ↓ 1.1 14 1

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

  • Filter: (isdefault = 'Y'::bpchar)
  • Rows Removed by Filter: 23
23. 359,828.650 359,828.650 ↑ 1.1 2,358 49,495

Index Scan using c_invoiceline_product on c_invoiceline invl (cost=0.44..102.63 rows=2,527 width=25) (actual time=0.043..7.270 rows=2,358 loops=49,495)

  • Index Cond: (m_product_id = mp.m_product_id)
24. 7.540 54.927 ↓ 20.1 20,186 1

Hash (cost=23,768.86..23,768.86 rows=1,006 width=23) (actual time=54.927..54.927 rows=20,186 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1380kB
25. 10.838 47.387 ↓ 20.1 20,186 1

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

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

Index Scan using invdateacct on c_invoice inv (cost=0.44..23,703.45 rows=13,480 width=25) (actual time=0.044..36.479 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,
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (ad_client_id = '1000000'::numeric) AND (issotrx = 'Y'::bpchar) AND (ad_org_id = ANY ('{0,1000020,1000009,
  • Rows Removed by Filter: 176
27. 0.009 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
28. 0.061 0.061 ↑ 1.0 10 1

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

  • Filter: (docbasetype = ANY ('{ARI,ARC}'::bpchar[]))
  • Rows Removed by Filter: 107
29. 0.011 0.017 ↓ 1.6 22 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.006 0.006 ↓ 1.6 22 1

Seq Scan on c_taxcategory tc (cost=0.00..1.14 rows=14 width=16) (actual time=0.005..0.006 rows=22 loops=1)

31. 0.016 0.038 ↑ 1.0 28 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
32. 0.022 0.022 ↑ 1.0 28 1

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

Planning time : 18.716 ms
Execution time : 489,691.813 ms