explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oK8N

Settings
# exclusive inclusive rows x rows loops node
1. 13.256 3,366.075 ↓ 2.0 2 1

Nested Loop Semi Join (cost=59,789.43..121,388.87 rows=1 width=33) (actual time=2,908.244..3,366.075 rows=2 loops=1)

  • Buffers: shared hit=144,982 read=24,460, temp read=17,147 written=25,923
2. 0.136 20.539 ↓ 5.0 5 1

Bitmap Heap Scan on m_offer pricingadj0_ (cost=4.57..35.10 rows=1 width=33) (actual time=5.849..20.539 rows=5 loops=1)

  • Recheck Cond: (((m_offer_type_id)::text = '875A33C88CC1444691BD3DD3BB337B2F'::text) AND (isactive = 'Y'::bpchar))
  • Filter: (((ad_client_id)::text = '20AE5337AF18478688937D0C81F9B0F9'::text) AND ((ad_org_id)::text = ANY ('{0,A6D971409BAB4D79929169506DC384CA,6D8A2FE703D3496882C94D1747FEB0FF,EA9C10D21637435FBE83479E16CDC0C7}'::text[])) AND ((((org_selection)::text = 'Y'::text) AND (NOT (alternatives: SubPlan 1 or hashed SubPlan 2))) OR (((org_selection)::text = 'N'::text) AND (alternatives: SubPlan 3 or hashed SubPlan 4))))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=5
  • Buffers: shared hit=20 read=3
3. 0.004 0.110 ↓ 0.0 0 1

BitmapAnd (cost=4.57..4.57 rows=5 width=0) (actual time=0.110..0.110 rows=0 loops=1)

  • Buffers: shared hit=6
4. 0.067 0.067 ↓ 1.0 51 1

Bitmap Index Scan on em_obpos_m_offer_offertype (cost=0.00..1.75 rows=50 width=0) (actual time=0.067..0.067 rows=51 loops=1)

  • Index Cond: ((m_offer_type_id)::text = '875A33C88CC1444691BD3DD3BB337B2F'::text)
  • Buffers: shared hit=3
5. 0.039 0.039 ↓ 1.0 162 1

Bitmap Index Scan on em_obpos_m_offer_active (cost=0.00..2.56 rows=158 width=0) (actual time=0.039..0.039 rows=162 loops=1)

  • Index Cond: (isactive = 'Y'::bpchar)
  • Buffers: shared hit=3
6.          

SubPlan (for Bitmap Heap Scan)

7. 20.270 20.270 ↓ 0.0 0 5

Index Scan using m_offer_org_unique on m_offer_organization pricingadj3_ (cost=0.28..2.50 rows=1 width=0) (actual time=4.054..4.054 rows=0 loops=5)

  • Index Cond: (((ad_org_id)::text = '6D8A2FE703D3496882C94D1747FEB0FF'::text) AND ((m_offer_id)::text = (pricingadj0_.m_offer_id)::text))
  • Filter: (isactive = 'Y'::bpchar)
  • Buffers: shared hit=7 read=3
8. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on m_offer_organization pricingadj3__1 (cost=3.52..92.76 rows=134 width=32) (never executed)

  • Recheck Cond: ((ad_org_id)::text = '6D8A2FE703D3496882C94D1747FEB0FF'::text)
  • Filter: (isactive = 'Y'::bpchar)
9. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on m_offer_org_unique (cost=0.00..3.48 rows=134 width=0) (never executed)

  • Index Cond: ((ad_org_id)::text = '6D8A2FE703D3496882C94D1747FEB0FF'::text)
10. 0.023 0.023 ↓ 0.0 0 1

Index Scan using m_offer_org_unique on m_offer_organization pricingadj4_ (cost=0.28..2.50 rows=1 width=0) (actual time=0.023..0.023 rows=0 loops=1)

  • Index Cond: (((ad_org_id)::text = '6D8A2FE703D3496882C94D1747FEB0FF'::text) AND ((m_offer_id)::text = (pricingadj0_.m_offer_id)::text))
  • Filter: (isactive = 'Y'::bpchar)
  • Buffers: shared hit=2
11. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on m_offer_organization pricingadj4__1 (cost=3.52..92.76 rows=134 width=32) (never executed)

  • Recheck Cond: ((ad_org_id)::text = '6D8A2FE703D3496882C94D1747FEB0FF'::text)
  • Filter: (isactive = 'Y'::bpchar)
12. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on m_offer_org_unique (cost=0.00..3.48 rows=134 width=0) (never executed)

  • Index Cond: ((ad_org_id)::text = '6D8A2FE703D3496882C94D1747FEB0FF'::text)
13. 365.290 3,332.280 ↓ 0.0 0 5

Hash Anti Join (cost=59,784.86..120,893.60 rows=46,017 width=33) (actual time=666.456..666.456 rows=0 loops=5)

  • Hash Cond: ((obdiscp_co1_.obdiscp_coupon_id)::text = (truisd_exp2_.obdiscp_coupon_id)::text)
  • Buffers: shared hit=144,962 read=24,457, temp read=17,147 written=25,909
14. 332.915 617.230 ↑ 1.2 47,087 5

Bitmap Heap Scan on obdiscp_coupon obdiscp_co1_ (cost=911.04..53,525.35 rows=55,473 width=66) (actual time=58.265..123.446 rows=47,087 loops=5)

  • Recheck Cond: ((m_offer_id)::text = (pricingadj0_.m_offer_id)::text)
  • Filter: ((status)::text = 'NU'::text)
  • Rows Removed by Filter: 1,741
  • Heap Blocks: exact=29,390Buffers: shared hit=6,697 read=24,457
15. 284.315 284.315 ↑ 1.2 48,845 5

Bitmap Index Scan on obdiscp_coupon_m_offer (cost=0.00..897.17 rows=58,095 width=0) (actual time=56.863..56.863 rows=48,845 loops=5)

  • Index Cond: ((m_offer_id)::text = (pricingadj0_.m_offer_id)::text)
  • Buffers: shared hit=14 read=1,750
16. 1,034.515 2,349.760 ↑ 1.0 838,048 5

Hash (cost=41,776.38..41,776.38 rows=841,716 width=33) (actual time=469.952..469.952 rows=838,048 loops=5)

  • Buckets: 262,144 Batches: 8 Memory Usage: 8,690kB
  • Buffers: shared hit=138,265, temp written=23,710
17. 1,315.245 1,315.245 ↑ 1.0 838,048 5

Seq Scan on truisd_exportedorgs truisd_exp2_ (cost=0.00..41,776.38 rows=841,716 width=33) (actual time=0.008..263.049 rows=838,048 loops=5)

  • Filter: ((ad_org_id)::text = '6D8A2FE703D3496882C94D1747FEB0FF'::text)
  • Rows Removed by Filter: 291,833
  • Buffers: shared hit=138,265
Planning time : 62.706 ms
Execution time : 3,374.212 ms