explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sHK7 : Optimization for: plan #GOMx

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.180 118,517.579 ↑ 11.2 8 1

WindowAgg (cost=365,581.00..713,236.82 rows=90 width=240) (actual time=21,088.522..118,517.579 rows=8 loops=1)

2. 8.553 6,672.943 ↑ 11.2 8 1

Sort (cost=365,581.00..365,581.22 rows=90 width=240) (actual time=6,672.934..6,672.943 rows=8 loops=1)

  • Sort Key: usr.username
  • Sort Method: quicksort Memory: 28kB
3. 0.115 6,664.390 ↑ 11.2 8 1

HashAggregate (cost=17,922.93..365,578.08 rows=90 width=240) (actual time=6,664.382..6,664.390 rows=8 loops=1)

4. 0.543 6,664.275 ↑ 8.2 11 1

Hash Join (cost=3,801.89..17,919.10 rows=90 width=240) (actual time=2,603.950..6,664.275 rows=11 loops=1)

  • Hash Cond: ((co.em_custsdt_documenttype_id)::text = (ct.custsdt_documenttype_id)::text)
5. 2.944 6,663.619 ↑ 1.4 740 1

Hash Join (cost=3,799.25..17,911.60 rows=1,057 width=273) (actual time=1,003.977..6,663.619 rows=740 loops=1)

  • Hash Cond: ((mp.m_product_category_id)::text = (mpc.m_product_category_id)::text)
6. 2.940 6,643.492 ↑ 1.4 740 1

Nested Loop (cost=3,097.33..17,188.54 rows=1,057 width=240) (actual time=986.747..6,643.492 rows=740 loops=1)

7. 2.615 5,569.772 ↑ 1.4 740 1

Nested Loop (cost=3,096.91..15,722.42 rows=1,057 width=187) (actual time=934.236..5,569.772 rows=740 loops=1)

8. 1.400 3,950.784 ↑ 1.6 319 1

Nested Loop Left Join (cost=3,096.35..6,580.26 rows=497 width=172) (actual time=920.060..3,950.784 rows=319 loops=1)

9. 2.101 3,229.082 ↑ 1.6 319 1

Nested Loop (cost=3,095.80..5,810.76 rows=497 width=187) (actual time=900.300..3,229.082 rows=319 loops=1)

  • Join Filter: ((co.c_bpartner_id)::text = (usr.c_bpartner_id)::text)
10. 1.578 2,511.196 ↑ 1.4 357 1

Nested Loop (cost=3,095.24..5,038.58 rows=497 width=186) (actual time=900.195..2,511.196 rows=357 loops=1)

11. 1.177 743.539 ↑ 1.4 357 1

Nested Loop (cost=3,094.69..3,661.04 rows=497 width=143) (actual time=670.428..743.539 rows=357 loops=1)

12. 0.077 0.077 ↑ 1.0 1 1

Index Scan using ad_org_key on ad_org st (cost=0.14..2.36 rows=1 width=36) (actual time=0.072..0.077 rows=1 loops=1)

  • Index Cond: ((ad_org_id)::text = '49BDB09F04DC46BFBCC8168D06813859'::text)
13. 179.795 742.285 ↑ 1.4 357 1

Bitmap Heap Scan on c_order co (cost=3,094.54..3,653.71 rows=497 width=172) (actual time=670.323..742.285 rows=357 loops=1)

  • Recheck Cond: ((em_poss_businessdate >= to_date('2018-12-18'::text, 'YYYY-MM-DD'::text)) AND (em_poss_businessdate <= to_date('2018-12-18'::text, 'YYYY-MM-DD'::text)) AND ((ad_client_id)::text = '8A3CC658963A4B7D8DA2490A3C1A7C9A'::text) AND ((ad_org_id)::text = '49BDB09F04DC46BFBCC8168D06813859'::text))
  • Filter: ((issotrx = 'Y'::bpchar) AND (isactive = 'Y'::bpchar))
14. 2.083 562.490 ↓ 0.0 0 1

BitmapAnd (cost=3,094.54..3,094.54 rows=497 width=0) (actual time=562.490..562.490 rows=0 loops=1)

15. 18.758 18.758 ↑ 1.2 7,355 1

Bitmap Index Scan on em_poss_order_businessdate (cost=0.00..111.92 rows=8,509 width=0) (actual time=18.758..18.758 rows=7,355 loops=1)

  • Index Cond: ((em_poss_businessdate >= to_date('2018-12-18'::text, 'YYYY-MM-DD'::text)) AND (em_poss_businessdate <= to_date('2018-12-18'::text, 'YYYY-MM-DD'::text)))
16. 541.649 541.649 ↓ 1.0 111,389 1

Bitmap Index Scan on c_order_client_org_date_docno (cost=0.00..2,982.12 rows=106,427 width=0) (actual time=541.649..541.649 rows=111,389 loops=1)

  • Index Cond: (((ad_client_id)::text = '8A3CC658963A4B7D8DA2490A3C1A7C9A'::text) AND ((ad_org_id)::text = '49BDB09F04DC46BFBCC8168D06813859'::text))
17. 1,766.079 1,766.079 ↑ 1.0 1 357

Index Scan using c_bpartner_key on c_bpartner bp (cost=0.56..2.76 rows=1 width=43) (actual time=4.947..4.947 rows=1 loops=357)

  • Index Cond: ((c_bpartner_id)::text = (co.c_bpartner_id)::text)
18. 715.785 715.785 ↑ 1.0 1 357

Index Scan using ad_user_partner on ad_user usr (cost=0.56..1.54 rows=1 width=67) (actual time=2.004..2.005 rows=1 loops=357)

  • Index Cond: ((c_bpartner_id)::text = (bp.c_bpartner_id)::text)
19. 720.302 720.302 ↑ 1.0 1 319

Index Scan using c_bplocation_bpartner on c_bpartner_location loc (cost=0.56..1.54 rows=1 width=51) (actual time=2.253..2.258 rows=1 loops=319)

  • Index Cond: ((c_bpartner_id)::text = (bp.c_bpartner_id)::text)
20. 1,616.373 1,616.373 ↑ 7.5 2 319

Index Scan using c_orderline_order on c_orderline col (cost=0.56..18.24 rows=15 width=81) (actual time=4.868..5.067 rows=2 loops=319)

  • Index Cond: ((c_order_id)::text = (co.c_order_id)::text)
21. 1,070.780 1,070.780 ↑ 1.0 1 740

Index Scan using m_product_key on m_product mp (cost=0.42..1.38 rows=1 width=119) (actual time=1.432..1.447 rows=1 loops=740)

  • Index Cond: ((m_product_id)::text = (col.m_product_id)::text)
22. 7.911 17.183 ↑ 1.0 13,152 1

Hash (cost=537.52..537.52 rows=13,152 width=66) (actual time=17.183..17.183 rows=13,152 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1259kB
23. 9.272 9.272 ↑ 1.0 13,152 1

Seq Scan on m_product_category mpc (cost=0.00..537.52 rows=13,152 width=66) (actual time=0.020..9.272 rows=13,152 loops=1)

24. 0.006 0.113 ↑ 1.0 4 1

Hash (cost=2.59..2.59 rows=4 width=33) (actual time=0.113..0.113 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
25. 0.107 0.107 ↑ 1.0 4 1

Seq Scan on custsdt_documenttype ct (cost=0.00..2.59 rows=4 width=33) (actual time=0.013..0.107 rows=4 loops=1)

  • Filter: ((value)::text = 'BP'::text)
  • Rows Removed by Filter: 43
26.          

SubPlan (forWindowAgg)

27. 111,844.208 111,844.456 ↑ 1.0 1 8

Seq Scan on m_product_category mpt (cost=4.42..3,862.83 rows=1 width=16) (actual time=3,409.079..13,980.557 rows=1 loops=8)

  • Filter: ((m_product_category_id)::text = (ad_get_node_tree_bylevel($1, mp.m_product_category_id, 0::numeric))::text)
  • Rows Removed by Filter: 13151
28.          

Initplan (forSeq Scan)

29. 0.248 0.248 ↑ 1.0 1 8

Seq Scan on ad_tree (cost=0.00..4.42 rows=1 width=30) (actual time=0.014..0.031 rows=1 loops=8)

  • Filter: (((ad_client_id)::text = (mpc.ad_client_id)::text) AND ((treetype)::text = 'PC'::text))
  • Rows Removed by Filter: 94