explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pPQo : con join lateral

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.028 1,504.651 ↓ 8.0 8 1

WindowAgg (cost=5,521.75..5,521.77 rows=1 width=256) (actual time=1,504.636..1,504.651 rows=8 loops=1)

2. 0.068 1,504.623 ↓ 8.0 8 1

Sort (cost=5,521.75..5,521.76 rows=1 width=256) (actual time=1,504.623..1,504.623 rows=8 loops=1)

  • Sort Key: usr.username
  • Sort Method: quicksort Memory: 29kB
3. 0.090 1,504.555 ↓ 8.0 8 1

HashAggregate (cost=5,521.73..5,521.74 rows=1 width=256) (actual time=1,504.553..1,504.555 rows=8 loops=1)

4. 0.043 1,504.465 ↓ 11.0 11 1

Nested Loop Left Join (cost=3,674.21..5,521.69 rows=1 width=256) (actual time=228.462..1,504.465 rows=11 loops=1)

5. 1,399.626 1,504.081 ↓ 11.0 11 1

Nested Loop (cost=3,673.66..5,520.14 rows=1 width=271) (actual time=228.415..1,504.081 rows=11 loops=1)

  • Join Filter: ((d.m_product_category_id)::text = (ad_get_node_tree_bylevel(t.ad_tree_id, mp.m_product_category_id, 0::numeric))::text)
  • Rows Removed by Join Filter: 2299
6. 0.019 84.633 ↑ 8.2 11 1

Nested Loop (cost=3,099.70..4,852.31 rows=90 width=255) (actual time=81.704..84.633 rows=11 loops=1)

7. 0.045 84.394 ↑ 8.2 11 1

Nested Loop (cost=3,099.41..4,740.65 rows=90 width=222) (actual time=81.658..84.394 rows=11 loops=1)

8. 0.050 84.019 ↑ 8.2 11 1

Nested Loop (cost=3,098.99..4,615.70 rows=90 width=169) (actual time=81.602..84.019 rows=11 loops=1)

9. 0.019 83.729 ↑ 10.5 4 1

Nested Loop (cost=3,098.43..3,843.13 rows=42 width=154) (actual time=81.516..83.729 rows=4 loops=1)

  • Join Filter: ((co.c_bpartner_id)::text = (usr.c_bpartner_id)::text)
10. 0.027 83.450 ↑ 10.5 4 1

Nested Loop (cost=3,097.88..3,777.82 rows=42 width=153) (actual time=81.435..83.450 rows=4 loops=1)

11. 0.023 83.239 ↑ 10.5 4 1

Nested Loop (cost=3,097.32..3,661.41 rows=42 width=110) (actual time=81.350..83.239 rows=4 loops=1)

12. 0.068 0.068 ↑ 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.066..0.068 rows=1 loops=1)

  • Index Cond: ((ad_org_id)::text = '49BDB09F04DC46BFBCC8168D06813859'::text)
13. 0.388 83.148 ↑ 10.5 4 1

Hash Join (cost=3,097.18..3,658.63 rows=42 width=139) (actual time=81.266..83.148 rows=4 loops=1)

  • Hash Cond: ((co.em_custsdt_documenttype_id)::text = (ct.custsdt_documenttype_id)::text)
14. 2.530 82.707 ↑ 1.4 357 1

Bitmap Heap Scan on c_order co (cost=3,094.54..3,653.71 rows=497 width=172) (actual time=80.307..82.707 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))
15. 1.654 80.177 ↓ 0.0 0 1

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

16. 2.107 2.107 ↑ 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=2.107..2.107 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)))
17. 76.416 76.416 ↓ 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=76.416..76.416 rows=111,389 loops=1)

  • Index Cond: (((ad_client_id)::text = '8A3CC658963A4B7D8DA2490A3C1A7C9A'::text) AND ((ad_org_id)::text = '49BDB09F04DC46BFBCC8168D06813859'::text))
18. 0.011 0.053 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
19. 0.042 0.042 ↑ 1.0 4 1

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

  • Filter: ((value)::text = 'BP'::text)
  • Rows Removed by Filter: 43
20. 0.184 0.184 ↑ 1.0 1 4

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

  • Index Cond: ((c_bpartner_id)::text = (co.c_bpartner_id)::text)
21. 0.260 0.260 ↑ 1.0 1 4

Index Scan using ad_user_partner on ad_user usr (cost=0.56..1.54 rows=1 width=67) (actual time=0.063..0.065 rows=1 loops=4)

  • Index Cond: ((c_bpartner_id)::text = (bp.c_bpartner_id)::text)
22. 0.240 0.240 ↑ 5.0 3 4

Index Scan using c_orderline_order on c_orderline col (cost=0.56..18.24 rows=15 width=81) (actual time=0.051..0.060 rows=3 loops=4)

  • Index Cond: ((c_order_id)::text = (co.c_order_id)::text)
23. 0.330 0.330 ↑ 1.0 1 11

Index Scan using m_product_key on m_product mp (cost=0.42..1.38 rows=1 width=119) (actual time=0.029..0.030 rows=1 loops=11)

  • Index Cond: ((m_product_id)::text = (col.m_product_id)::text)
24. 0.220 0.220 ↑ 1.0 1 11

Index Scan using m_product_category_key on m_product_category mpc (cost=0.29..1.23 rows=1 width=66) (actual time=0.018..0.020 rows=1 loops=11)

  • Index Cond: ((m_product_category_id)::text = (mp.m_product_category_id)::text)
25. 0.598 19.822 ↓ 105.0 210 11

Materialize (cost=573.96..620.13 rows=2 width=79) (actual time=0.962..1.802 rows=210 loops=11)

26. 3.069 19.224 ↓ 105.0 210 1

Hash Join (cost=573.96..620.12 rows=2 width=79) (actual time=10.572..19.224 rows=210 loops=1)

  • Hash Cond: (((t.ad_client_id)::text = (d.ad_client_id)::text) AND ((tn.node_id)::text = (d.m_product_category_id)::text))
27. 1.851 5.838 ↓ 18.0 13,157 1

Nested Loop (cost=0.41..41.09 rows=729 width=91) (actual time=0.158..5.838 rows=13,157 loops=1)

28. 0.057 0.057 ↑ 1.0 5 1

Seq Scan on ad_tree t (cost=0.00..4.19 rows=5 width=59) (actual time=0.026..0.057 rows=5 loops=1)

  • Filter: ((treetype)::text = 'PC'::text)
  • Rows Removed by Filter: 90
29. 3.930 3.930 ↓ 16.5 2,631 5

Index Only Scan using ad_treenode_tree_node_un on ad_treenode tn (cost=0.41..5.79 rows=159 width=63) (actual time=0.046..0.786 rows=2,631 loops=5)

  • Index Cond: (ad_tree_id = (t.ad_tree_id)::text)
  • Heap Fetches: 0
30. 0.135 10.317 ↑ 1.0 210 1

Hash (cost=570.40..570.40 rows=210 width=82) (actual time=10.317..10.317 rows=210 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
31. 10.182 10.182 ↑ 1.0 210 1

Seq Scan on m_product_category d (cost=0.00..570.40 rows=210 width=82) (actual time=0.018..10.182 rows=210 loops=1)

  • Filter: ((em_custsha_category_type)::text = 'Department'::text)
  • Rows Removed by Filter: 12942
32. 0.341 0.341 ↑ 1.0 1 11

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

  • Index Cond: ((c_bpartner_id)::text = (bp.c_bpartner_id)::text)