explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2mRu : test 1

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 105,416.119 ↓ 0.0 0 1

Subquery Scan on data (cost=1,637,111.39..1,637,118.09 rows=536 width=381) (actual time=105,416.119..105,416.119 rows=0 loops=1)

2. 0.017 105,416.117 ↓ 0.0 0 1

Sort (cost=1,637,111.39..1,637,112.73 rows=536 width=462) (actual time=105,416.117..105,416.117 rows=0 loops=1)

  • Sort Key: otop.name, o.name, mot.name, mo.name
  • Sort Method: quicksort Memory: 25kB
3. 0.005 105,416.100 ↓ 0.0 0 1

HashAggregate (cost=1,636,808.38..1,637,087.10 rows=536 width=462) (actual time=105,416.100..105,416.100 rows=0 loops=1)

4. 0.001 105,416.095 ↓ 0.0 0 1

Nested Loop Left Join (cost=40.10..1,636,792.30 rows=536 width=462) (actual time=105,416.095..105,416.095 rows=0 loops=1)

5. 0.001 105,416.094 ↓ 0.0 0 1

Nested Loop Left Join (cost=35.45..1,634,284.68 rows=536 width=430) (actual time=105,416.094..105,416.094 rows=0 loops=1)

6. 0.019 105,416.093 ↓ 0.0 0 1

Nested Loop (cost=35.30..1,634,176.03 rows=536 width=396) (actual time=105,416.093..105,416.093 rows=0 loops=1)

7. 0.033 0.033 ↑ 1.0 1 1

Index Scan using m_offertype_key on m_offer_type mot (cost=0.14..2.36 rows=1 width=220) (actual time=0.031..0.033 rows=1 loops=1)

  • Index Cond: ((m_offer_type_id)::text = '7899A7A4204749AD92881133C4EE7A57'::text)
8. 0.002 105,416.041 ↓ 0.0 0 1

Merge Left Join (cost=35.16..1,634,168.32 rows=536 width=176) (actual time=105,416.041..105,416.041 rows=0 loops=1)

  • Merge Cond: ((o.ad_org_id)::text = (trn.node_id)::text)
9. 0.002 105,416.039 ↓ 0.0 0 1

Nested Loop (cost=34.89..1,634,129.55 rows=519 width=163) (actual time=105,416.039..105,416.039 rows=0 loops=1)

10. 190.385 105,416.037 ↓ 0.0 0 1

Nested Loop (cost=34.74..1,634,038.63 rows=519 width=158) (actual time=105,416.037..105,416.037 rows=0 loops=1)

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

Index Scan using ad_org_key on ad_org o (cost=0.17..21.24 rows=18 width=47) (actual time=0.019..28.084 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
12. 204.499 105,197.568 ↓ 5.1 17,396 21

Materialize (cost=34.58..1,633,100.67 rows=3,427 width=144) (actual time=4,715.454..5,009.408 rows=17,396 loops=21)

13. 1,069.512 104,993.069 ↓ 5.1 17,396 1

Nested Loop (cost=34.58..1,633,083.53 rows=3,427 width=144) (actual time=99,024.516..104,993.069 rows=17,396 loops=1)

14. 2,911.138 60,620.432 ↓ 2.5 1,732,125 1

Nested Loop (cost=34.02..836,892.08 rows=685,396 width=140) (actual time=1.313..60,620.432 rows=1,732,125 loops=1)

15. 2,515.265 4,013.419 ↓ 2.5 1,732,125 1

Hash Join (cost=33.46..118,759.24 rows=685,396 width=129) (actual time=1.265..4,013.419 rows=1,732,125 loops=1)

  • Hash Cond: ((solf.m_offer_id)::text = (mo.m_offer_id)::text)
16. 1,497.606 1,497.606 ↓ 1.0 2,026,397 1

Seq Scan on c_orderline_offer solf (cost=0.00..104,272.87 rows=2,026,387 width=76) (actual time=0.017..1,497.606 rows=2,026,397 loops=1)

17. 0.128 0.548 ↓ 1.1 209 1

Hash (cost=31.16..31.16 rows=184 width=86) (actual time=0.548..0.548 rows=209 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
18. 0.420 0.420 ↓ 1.1 209 1

Seq Scan on m_offer mo (cost=0.00..31.16 rows=184 width=86) (actual time=0.015..0.420 rows=209 loops=1)

  • Filter: ((isactive = 'Y'::bpchar) AND ((m_offer_type_id)::text = '7899A7A4204749AD92881133C4EE7A57'::text))
  • Rows Removed by Filter: 337
19. 53,695.875 53,695.875 ↑ 1.0 1 1,732,125

Index Scan using c_orderline_key on c_orderline sol (cost=0.56..1.04 rows=1 width=77) (actual time=0.030..0.031 rows=1 loops=1,732,125)

  • Index Cond: ((c_orderline_id)::text = (solf.c_orderline_id)::text)
20. 43,303.125 43,303.125 ↓ 0.0 0 1,732,125

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

  • 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
21. 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)
22. 0.000 0.000 ↓ 0.0 0

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

23. 0.000 0.000 ↓ 0.0 0

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

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

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

25. 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)
26. 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)