explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZfZQ

Settings
# exclusive inclusive rows x rows loops node
1. 67.251 13,392.275 ↓ 4.3 13 1

GroupAggregate (cost=244,676.14..244,677.81 rows=3 width=206) (actual time=13,327.295..13,392.275 rows=13 loops=1)

  • Group Key: og.name
2. 78.407 13,325.024 ↓ 25,154.3 75,463 1

Sort (cost=244,676.14..244,676.15 rows=3 width=206) (actual time=13,316.249..13,325.024 rows=75,463 loops=1)

  • Sort Key: og.name
  • Sort Method: quicksort Memory: 9245kB
3. 61.738 13,246.617 ↓ 25,154.3 75,463 1

Nested Loop (cost=86.24..244,676.12 rows=3 width=206) (actual time=0.585..13,246.617 rows=75,463 loops=1)

4. 17.412 13,033.953 ↓ 25,154.3 75,463 1

Nested Loop (cost=86.10..244,675.62 rows=3 width=74) (actual time=0.568..13,033.953 rows=75,463 loops=1)

5. 0.007 0.051 ↑ 1.0 1 1

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

6. 0.034 0.034 ↑ 1.0 1 1

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

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

  • Index Cond: (c_currency_id = pl.c_currency_id)
8. 130.780 13,016.490 ↓ 25,154.3 75,463 1

Nested Loop Left Join (cost=85.96..244,670.22 rows=3 width=62) (actual time=0.515..13,016.490 rows=75,463 loops=1)

  • -> Index Scan using c_uom_conversion_product on c_uom_conversion uomc (cost=0.12..0.15 rows=1 width=64) (actual time=0.001..0.001
9. 205.432 12,885.710 ↓ 25,154.3 75,463 1

Nested Loop (cost=85.83..244,669.75 rows=3 width=42) (actual time=0.501..12,885.710 rows=75,463 loops=1)

  • -> Index Only Scan using c_taxcategory_pkey on c_taxcategory tc (cost=0.14..0.15 rows=1 width=16) (actual time=0.002..0.002
  • Index Cond: ((c_uom_to_id = pp.c_uom_id) AND (m_product_id = pp.m_product_id))
10. 11,632.701 12,680.278 ↓ 25,154.3 75,463 1

Nested Loop (cost=85.70..244,669.26 rows=3 width=54) (actual time=0.476..12,680.278 rows=75,463 loops=1)

  • Join Filter: (mp.m_product_id = pp.m_product_id)
  • -> Index Scan using m_productprice_pkey on m_productprice pp (cost=79.61..79.63 rows=1 width=29) (actual time=0.005..
  • Index Cond: (c_taxcategory_id = mp.c_taxcategory_id)
  • Heap Fetches: 75463
11. 0.000 1,047.577 ↓ 54.8 75,463 1

Nested Loop (cost=6.09..135,000.39 rows=1,377 width=55) (actual time=0.197..1,047.577 rows=75,463 loops=1)

  • Join Filter: (mp.c_taxcategory_id = tx.c_taxcategory_id)
  • Rows Removed by Join Filter: 981019
  • Index Cond: ((m_product_id = invl.m_product_id) AND (m_pricelist_version_id = (SubPlan 1)))
  • Filter: (ad_client_id = '1000000'::numeric)
12. 415.319 702.721 ↓ 19.4 75,463 1

Nested Loop (cost=6.09..134,240.73 rows=3,888 width=45) (actual time=0.177..702.721 rows=75,463 loops=1)

  • -> Index Scan using m_product_pkey on m_product mp (cost=0.29..0.31 rows=1 width=13) (actual time=0.004..
13. 287.343 287.402 ↓ 19.4 75,468 1

Nested Loop (cost=5.80..133,013.26 rows=3,895 width=32) (actual time=0.152..287.402 rows=75,468 loops=1)

  • -> Hash Join (cost=5.24..23720.16 rows=1012 width=23) (actual time=0.124..73.063 rows=20186 loops=1
  • Hash Cond: (inv.c_doctype_id = dt.c_doctype_id)
  • -> Index Scan using invdateacct on c_invoice inv (cost=0.44..23654.40 rows=13558 width=25) (a
  • Index Cond: ((dateacct >= to_timestamp('2018-04-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:S
  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (issotrx = 'Y'::bpchar) AND (ad_clie
  • Rows Removed by Filter: 176
  • -> Index Scan using c_invoiceline_invoice on c_invoiceline invl (cost=0.56..106.51 rows=149 width=2
  • Index Cond: (c_invoice_id = inv.c_invoice_id)
  • Index Cond: (m_product_id = invl.m_product_id)
14. 0.059 0.059 ↑ 1.0 10 1

Hash (cost=4.67..4.67 rows=10 width=10) (actual time=0.059..0.059 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.009..0.05
  • Filter: (docbasetype = ANY ('{ARI,ARC}'::bpchar[]))
  • Rows Removed by Filter: 107
15. 75.441 75.463 ↓ 1.1 14 75,463

Materialize (cost=0.00..1.53 rows=13 width=10) (actual time=0.000..0.001 rows=14 loops=75,463)

16. 0.022 0.022 ↓ 1.1 14 1

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

  • Filter: (isdefault = 'Y'::bpchar)
  • Rows Removed by Filter: 23
17.          

SubPlan (forNested Loop)

18. 0.000 11,093.061 ↑ 1.0 1 75,463

Limit (cost=79.18..79.19 rows=1 width=15) (actual time=0.147..0.147 rows=1 loops=75,463)

19. 377.315 11,093.061 ↑ 2.0 1 75,463

Sort (cost=79.18..79.19 rows=2 width=15) (actual time=0.147..0.147 rows=1 loops=75,463)

  • Sort Key: plvin.validfrom DESC
  • Sort Method: quicksort Memory: 25kB
20. 10,715.746 10,715.746 ↓ 2.5 5 75,463

Nested Loop (cost=0.71..79.17 rows=2 width=15) (actual time=0.018..0.142 rows=5 loops=75,463)

  • -> Index Only Scan using m_productprice_pkey on m_productprice ppr (cost=0.42..22.73 rows=1
  • Index Cond: (m_product_id = invl.m_product_id)
  • Heap Fetches: 2412489
  • -> Index Scan using m_pricelist_version_pkey on m_pricelist_version plvin (cost=0.29..4.02
  • 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
21. 0.000 11,093.061 ↑ 1.0 1 75,463

Limit (cost=79.18..79.19 rows=1 width=15) (actual time=0.147..0.147 rows=1 loops=75,463)

22. 377.315 11,093.061 ↑ 2.0 1 75,463

Sort (cost=79.18..79.19 rows=2 width=15) (actual time=0.147..0.147 rows=1 loops=75,463)

  • Sort Key: plvin.validfrom DESC
  • Sort Method: quicksort Memory: 25kB
23. 10,715.746 10,715.746 ↓ 2.5 5 75,463

Nested Loop (cost=0.71..79.17 rows=2 width=15) (actual time=0.018..0.142 rows=5 loops=75,463)

  • -> Index Only Scan using m_productprice_pkey on m_productprice ppr (cost=0.42..22.73 rows=1
  • Index Cond: (m_product_id = invl.m_product_id)
  • Heap Fetches: 2412489
  • -> Index Scan using m_pricelist_version_pkey on m_pricelist_version plvin (cost=0.29..4.02
  • 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
24. 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)