explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H482 : Optimization for: plan #cvUo

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=118,807.36..118,807.36 rows=2 width=572) (actual rows= loops=)

  • Sort Key: sr.name, foo.name
2. 0.000 0.000 ↓ 0.0

Group (cost=118,807.32..118,807.35 rows=2 width=572) (actual rows= loops=)

  • Group Key: t.name, sr.c_salesregion_id, foo.name, foo.counter, cev.value
3. 0.000 0.000 ↓ 0.0

Sort (cost=118,807.32..118,807.32 rows=2 width=572) (actual rows= loops=)

  • Sort Key: t.name, sr.c_salesregion_id, foo.name, foo.counter, cev.value
4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=118,791.10..118,807.31 rows=2 width=572) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=118,790.96..118,800.97 rows=2 width=516) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Hash Join (cost=118,790.82..118,794.64 rows=2 width=460) (actual rows= loops=)

  • Hash Cond: ((sr.c_salesregion_id)::text = (foo.zona)::text)
7. 0.000 0.000 ↓ 0.0

Seq Scan on c_salesregion sr (cost=0.00..3.58 rows=58 width=60) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Hash (cost=118,790.80..118,790.80 rows=2 width=482) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Subquery Scan on foo (cost=118,790.75..118,790.80 rows=2 width=482) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Unique (cost=118,790.75..118,790.78 rows=2 width=482) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Sort (cost=118,790.75..118,790.75 rows=2 width=482) (actual rows= loops=)

  • Sort Key: bp.value, bp.em_cuvddm_cec_id, p.name, bp.em_cuvddm_csalesregionid, bp.em_pm_territorial_id
12. 0.000 0.000 ↓ 0.0

Append (cost=18,061.20..118,790.74 rows=2 width=482) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Unique (cost=18,061.20..18,061.21 rows=1 width=141) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Sort (cost=18,061.20..18,061.20 rows=1 width=141) (actual rows= loops=)

  • Sort Key: bp.value, bp.em_cuvddm_cec_id, p.name, bp.em_cuvddm_csalesregionid
15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.81..18,061.19 rows=1 width=141) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.40..18,060.73 rows=1 width=63) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.98..18,057.61 rows=7 width=66) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Index Scan using smfpda_visit_bp_visitdate on smfpda_visit v (cost=0.42..17,837.11 rows=1 width=33) (actual rows= loops=)

  • Index Cond: ((visitdate >= '2020-01-01 00:00:00'::timestamp without time zone) AND (visitdate <= '2020-01-31 00:00:00'::timestamp without time zone))
19. 0.000 0.000 ↓ 0.0

Index Scan using smfpda_prod_visit on smfpda_productinfo stk (cost=0.56..219.36 rows=114 width=99) (actual rows= loops=)

  • Index Cond: ((smfpda_visit_id)::text = (v.smfpda_visit_id)::text)
  • Filter: (quantity > '0'::numeric)
20. 0.000 0.000 ↓ 0.0

Index Scan using m_product_key on m_product p (cost=0.41..0.44 rows=1 width=63) (actual rows= loops=)

  • Index Cond: ((m_product_id)::text = (stk.m_product_id)::text)
  • Filter: (((value)::text ~~ '030%'::text) AND (em_vd_isvd = 'Y'::bpchar))
21. 0.000 0.000 ↓ 0.0

Index Scan using c_bpartner_key on c_bpartner bp (cost=0.42..0.45 rows=1 width=144) (actual rows= loops=)

  • Index Cond: ((c_bpartner_id)::text = (stk.c_bpartner_id)::text)
  • Filter: (((value)::text ~~ '010PV%'::text) AND ((em_smfpda_placetype)::text = 'E'::text) AND (em_cuvddm_deactstatus = '1'::bpchar) AND ((em_pm_territorial_id)::text = 'FB (...)
22. 0.000 0.000 ↓ 0.0

Unique (cost=100,729.49..100,729.50 rows=1 width=141) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Sort (cost=100,729.49..100,729.49 rows=1 width=141) (actual rows= loops=)

  • Sort Key: bp_1.value, bp_1.em_cuvddm_cec_id, p_1.name, bp_1.em_cuvddm_csalesregionid
24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,478.47..100,729.48 rows=1 width=141) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,478.05..100,723.85 rows=3 width=63) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,477.50..100,337.22 rows=608 width=63) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on m_product p_1 (cost=0.00..2,000.91 rows=1 width=63) (actual rows= loops=)

  • Filter: (((value)::text ~~ '030%'::text) AND (em_vd_isvd = 'Y'::bpchar))
28. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on c_invoiceline il (cost=1,477.50..97,609.29 rows=72,702 width=66) (actual rows= loops=)

  • Recheck Cond: ((m_product_id)::text = (p_1.m_product_id)::text)
29. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on c_invoiceline_product (cost=0.00..1,459.32 rows=72,702 width=0) (actual rows= loops=)

  • Index Cond: ((m_product_id)::text = (p_1.m_product_id)::text)
30. 0.000 0.000 ↓ 0.0

Index Scan using c_invoice_key on c_invoice i (cost=0.55..0.63 rows=1 width=66) (actual rows= loops=)

  • Index Cond: ((c_invoice_id)::text = (il.c_invoice_id)::text)
  • Filter: ((isactive = 'Y'::bpchar) AND (issotrx = 'Y'::bpchar) AND (em_vd_isvd = 'Y'::bpchar) AND ((dateinvoiced)::date >= '2020-01-01'::date) AND ((dateinvoiced)::d (...)
31. 0.000 0.000 ↓ 0.0

Index Scan using c_bpartner_key on c_bpartner bp_1 (cost=0.42..1.87 rows=1 width=144) (actual rows= loops=)

  • Index Cond: ((c_bpartner_id)::text = (i.em_cuvdinv_retailer)::text)
  • Filter: (((value)::text ~~ '010PV%'::text) AND ((em_smfpda_placetype)::text = 'E'::text) AND (em_cuvddm_deactstatus = '1'::bpchar) AND ((em_pm_territorial_id)::text = 'FB (...)
32. 0.000 0.000 ↓ 0.0

Index Scan using cuvddm_cec_key on cuvddm_cec cev (cost=0.14..3.16 rows=1 width=220) (actual rows= loops=)

  • Index Cond: ((cuvddm_cec_id)::text = (foo.client)::text)
33. 0.000 0.000 ↓ 0.0

Index Scan using pm_territorial_id on pm_territorial t (cost=0.14..3.16 rows=1 width=220) (actual rows= loops=)

  • Index Cond: ((pm_territorial_id)::text = (foo.terr)::text)