explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GBaa : discouts_1m_ComboFilter

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 246,321.999 ↓ 0.0 0 1

Sort (cost=1,637,011.67..1,637,012.95 rows=514 width=268) (actual time=246,321.999..246,321.999 rows=0 loops=1)

  • Sort Key: otop.name, o.name, mot.name, mo.name
  • Sort Method: quicksort Memory: 25kB
2. 0.004 246,321.979 ↓ 0.0 0 1

HashAggregate (cost=1,636,721.24..1,636,988.52 rows=514 width=268) (actual time=246,321.979..246,321.979 rows=0 loops=1)

3. 0.002 246,321.975 ↓ 0.0 0 1

Nested Loop Left Join (cost=41.26..1,636,705.82 rows=514 width=268) (actual time=246,321.975..246,321.975 rows=0 loops=1)

4. 0.000 246,321.973 ↓ 0.0 0 1

Nested Loop Left Join (cost=36.61..1,634,301.14 rows=514 width=236) (actual time=246,321.973..246,321.973 rows=0 loops=1)

5. 0.022 246,321.973 ↓ 0.0 0 1

Nested Loop (cost=36.35..1,634,119.79 rows=514 width=202) (actual time=246,321.973..246,321.973 rows=0 loops=1)

6. 0.029 0.029 ↑ 1.0 1 1

Seq Scan on m_offer_type mot (cost=0.00..1.25 rows=1 width=59) (actual time=0.024..0.029 rows=1 loops=1)

  • Filter: ((m_offer_type_id)::text = '7899A7A4204749AD92881133C4EE7A57'::text)
  • Rows Removed by Filter: 19
7. 0.002 246,321.922 ↓ 0.0 0 1

Merge Left Join (cost=36.35..1,634,113.40 rows=514 width=176) (actual time=246,321.922..246,321.922 rows=0 loops=1)

  • Merge Cond: ((o.ad_org_id)::text = (trn.node_id)::text)
8. 0.001 246,321.920 ↓ 0.0 0 1

Nested Loop (cost=36.07..1,634,073.02 rows=498 width=163) (actual time=246,321.920..246,321.920 rows=0 loops=1)

9. 156.654 246,321.919 ↓ 0.0 0 1

Nested Loop (cost=35.93..1,633,985.78 rows=498 width=158) (actual time=246,321.919..246,321.919 rows=0 loops=1)

  • Join Filter: ((so.ad_org_id)::text = (o.ad_org_id)::text)
  • Rows Removed by Join Filter: 365316
10. 11.245 11.245 ↓ 1.2 21 1

Index Scan using ad_org_key on ad_org o (cost=0.29..30.60 rows=18 width=47) (actual time=0.021..11.245 rows=21 loops=1)

  • Index Cond: ((ad_org_id)::text = ANY ('{7B14F7FDF88041C79CFFC7B6C97EC98C,2A7F189D2B1744CAB44D9913782F9FE1,6BCC1141B95C4DE189C4D893FA83C499,18B7764347B54926A14363940A055000,B1E1E8F83F8A4AEF937A99904C87538E,2809743F5D544C00979F0E6E8B946917,DE31AA7923704DEEADC8753CAE26B305,15CE0C688FAC4030820AF3F168D8A71D,6446C23618E84B98BA55FB403530D53B,4F51CDBD07C541469462973979987E7B,2447906C5302433C9326F011029F016E,B12346108D6345A39C8CD0358E5C5F9F,2E1E62F6F5CF4C9889C7787BFEB825B3,E6DD6BBCE9CD4F7AB41305353DD449A1,E17DA950EBE94847A7D3C6AE6F714869,28F3639BF0CA4F409EA2263F9C7A0BA0,CFFDE27364084FE3A0D74798C4B69910,484B2B49CF46462BA87F78190C462E1E,3176605DD7844DB48AB99D1003A56ECA,4FECC7CB14B44037AC1988883C626168,EDB436D38F394D488F57FB701E86E4F8,47CFAFD4E73F42388790CE579AE05D2F}'::text[]))
  • Filter: (((ad_client_id)::text = '3AFE04DCE6EE4C5A9912EDFF5517C3A7'::text) AND ((em_obretco_retailorgtype)::text = 'S'::text))
  • Rows Removed by Filter: 1
11. 158.079 246,154.020 ↓ 5.1 17,396 21

Materialize (cost=35.63..1,633,038.45 rows=3,427 width=144) (actual time=11,515.960..11,721.620 rows=17,396 loops=21)

12. 2,156.716 245,995.941 ↓ 5.1 17,396 1

Nested Loop (cost=35.63..1,633,021.32 rows=3,427 width=144) (actual time=241,835.130..245,995.941 rows=17,396 loops=1)

13. 4,074.321 186,677.516 ↓ 2.5 1,732,173 1

Nested Loop (cost=35.08..836,867.33 rows=685,365 width=140) (actual time=8.065..186,677.516 rows=1,732,173 loops=1)

14. 2,521.753 4,189.376 ↓ 2.5 1,732,173 1

Hash Join (cost=34.52..118,763.98 rows=685,365 width=129) (actual time=2.966..4,189.376 rows=1,732,173 loops=1)

  • Hash Cond: ((solf.m_offer_id)::text = (mo.m_offer_id)::text)
15. 1,667.104 1,667.104 ↑ 1.0 2,026,454 1

Seq Scan on c_orderline_offer solf (cost=0.00..104,276.59 rows=2,026,459 width=76) (actual time=0.589..1,667.104 rows=2,026,454 loops=1)

16. 0.143 0.519 ↓ 1.1 209 1

Hash (cost=32.20..32.20 rows=185 width=86) (actual time=0.519..0.519 rows=209 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
17. 0.376 0.376 ↓ 1.1 209 1

Seq Scan on m_offer mo (cost=0.00..32.20 rows=185 width=86) (actual time=0.014..0.376 rows=209 loops=1)

  • Filter: ((isactive = 'Y'::bpchar) AND ((m_offer_type_id)::text = '7899A7A4204749AD92881133C4EE7A57'::text))
  • Rows Removed by Filter: 338
18. 178,413.819 178,413.819 ↑ 1.0 1 1,732,173

Index Scan using c_orderline_key on c_orderline sol (cost=0.56..1.04 rows=1 width=77) (actual time=0.102..0.103 rows=1 loops=1,732,173)

  • Index Cond: ((c_orderline_id)::text = (solf.c_orderline_id)::text)
19. 57,161.709 57,161.709 ↓ 0.0 0 1,732,173

Index Scan using c_order_key on c_order so (cost=0.56..1.15 rows=1 width=70) (actual time=0.033..0.033 rows=0 loops=1,732,173)

  • Index Cond: ((c_order_id)::text = (sol.c_order_id)::text)
  • Filter: ((isactive = 'Y'::bpchar) AND (issotrx = 'Y'::bpchar) AND (processed = 'Y'::bpchar) AND (trunc(em_poss_businessdate) >= '2018-09-01'::date) AND (trunc(em_poss_businessdate) <= '2018-10-01'::date))
  • Rows Removed by Filter: 1
20. 0.000 0.000 ↓ 0.0 0

Index Scan using c_currency_key on c_currency soc (cost=0.14..0.17 rows=1 width=9) (never executed)

  • Index Cond: ((c_currency_id)::text = (so.c_currency_id)::text)
21. 0.000 0.000 ↓ 0.0 0

Index Scan using em_obcrqan_treenode_nodeid_idx on ad_treenode trn (cost=0.28..32.34 rows=685 width=30) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Index Scan using ad_org_key on ad_org otop (cost=0.27..0.34 rows=1 width=47) (never executed)

  • Index Cond: ((trn.parent_id)::text = (ad_org_id)::text)
23. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=4.65..4.66 rows=1 width=5) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on obcombo_family mof (cost=1.40..4.64 rows=3 width=5) (never executed)

  • Recheck Cond: ((m_offer_id)::text = (mo.m_offer_id)::text)
25. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on obcombo_fam_offer_idx (cost=0.00..1.40 rows=3 width=0) (never executed)

  • Index Cond: ((m_offer_id)::text = (mo.m_offer_id)::text)