explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mCc6

Settings
# exclusive inclusive rows x rows loops node
1. 22,714.367 815,505.040 ↓ 177.0 531 1

Hash Right Join (cost=14,520,983.56..24,702,630.11 rows=3 width=389) (actual time=436,277.532..815,505.040 rows=531 loops=1)

  • Hash Cond: ((o.event_id)::text = (z.event_id)::text)
2. 385,630.089 385,630.089 ↓ 1.0 170,439,889 1

Seq Scan on event_offer o (cost=0.00..9,551,051.56 rows=168,158,656 width=100) (actual time=7.056..385,630.089 rows=170,439,889 loops=1)

3. 0.315 407,160.584 ↓ 531.0 531 1

Hash (cost=14,520,983.54..14,520,983.54 rows=1 width=289) (actual time=407,160.584..407,160.584 rows=531 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 138kB
4. 0.094 407,160.269 ↓ 531.0 531 1

Subquery Scan on z (cost=14,520,983.47..14,520,983.54 rows=1 width=289) (actual time=407,159.734..407,160.269 rows=531 loops=1)

5. 0.403 407,160.175 ↓ 531.0 531 1

GroupAggregate (cost=14,520,983.47..14,520,983.53 rows=1 width=346) (actual time=407,159.732..407,160.175 rows=531 loops=1)

  • Group Key: a.campaign, (replace((a.campaign)::text, ' '::text, ''::text)), a.campaign_id, a.ma_id, a.event_id, a.subs_id, a.msisdn, a.error_cd, a.error_msg, uc18_offer_matrix.nf_id, uc18_offer_matrix.nf_denom, fu.fulfill_channel_status|
6. 4.419 407,159.772 ↓ 531.0 531 1

Sort (cost=14,520,983.47..14,520,983.48 rows=1 width=210) (actual time=407,159.723..407,159.772 rows=531 loops=1)

  • Sort Key: a.campaign, (replace((a.campaign)::text, ' '::text, ''::text)), a.campaign_id, a.ma_id, a.event_id, a.subs_id, a.msisdn, a.error_cd, a.error_msg, uc18_offer_matrix.nf_id, uc18_offer_matrix.nf_denom, fu.fulfill_channel_s|
  • Sort Method: quicksort Memory: 166kB
7. 2.177 407,155.353 ↓ 531.0 531 1

Nested Loop Left Join (cost=1,026,615.67..14,520,983.46 rows=1 width=210) (actual time=372,994.221..407,155.353 rows=531 loops=1)

  • Join Filter: ((a.event_id)::text = (fu.event_id)::text)
  • Rows Removed by Join Filter: 531
8. 28.179 374,291.710 ↓ 531.0 531 1

Nested Loop Left Join (cost=0.56..13,494,358.57 rows=1 width=171) (actual time=340,134.094..374,291.710 rows=531 loops=1)

  • Join Filter: ((a.ma_id)::text = (uc18_offer_matrix.ma_id)::text)
  • Rows Removed by Join Filter: 297896
9. 372,423.616 373,956.082 ↓ 531.0 531 1

Nested Loop Left Join (cost=0.56..13,494,224.85 rows=1 width=163) (actual time=340,110.382..373,956.082 rows=531 loops=1)

  • Filter: (r.fulfill_channel_status IS NULL)
  • -> Seq Scan on campaign_reporting a (cost=0.00..13193309.16 rows=38439 width=163) (actual time=340106.114..372421.900 rows=531 loops=1) |
  • Filter: ((contact_dttm IS NOT NULL) AND (response_dttm IS NOT NULL) AND (((treatment_desc)::text = ANY ('{Invite,Fulfillment}'::text[])) OR (error_cd IS NOT NULL)) AND (control_grp_tag = 0) AND ((response_|
  • Rows Removed by Filter: 192848081
10. 1,532.466 1,532.466 ↓ 0.0 0 531

Index Scan using msisdn_idx on event_fulfill r (cost=0.56..7.82 rows=1 width=28) (actual time=2.886..2.886 rows=0 loops=531)

  • Index Cond: ((a.msisdn)::text = (msisdn)::text)
  • Filter: (((event_id)::text ~~ 'RP_%'::text) AND ((fulfill_channel_status)::text = 'SUCCESS'::text) AND (fulfill_dttm >= a.response_dttm) AND ((fulfill_dttm)::date >= '2019-08-15'::date) AND (fulfill_dttm <|
  • Rows Removed by Filter: 1
11. 24.426 307.449 ↑ 1.0 562 531

Append (cost=0.00..126.65 rows=565 width=45) (actual time=0.002..0.579 rows=562 loops=531)

12. 7.965 7.965 ↑ 1.0 40 531

Seq Scan on uc18_offer_matrix (cost=0.00..9.40 rows=40 width=53) (actual time=0.002..0.015 rows=40 loops=531)

13. 127.971 127.971 ↑ 1.0 216 531

Seq Scan on uc9_offer_matrix (cost=0.00..52.16 rows=216 width=51) (actual time=0.003..0.241 rows=216 loops=531)

14. 76.464 76.464 ↑ 1.0 134 531

Seq Scan on uc25_offer_matrix (cost=0.00..34.34 rows=134 width=26) (actual time=0.004..0.144 rows=134 loops=531)

15. 43.011 43.011 ↑ 1.1 112 531

Seq Scan on uc21_offer_matrix (cost=0.00..19.18 rows=118 width=37) (actual time=0.004..0.081 rows=112 loops=531)

16. 2.655 2.655 ↑ 1.0 2 531

Seq Scan on uc27_offer_matrix (cost=0.00..1.02 rows=2 width=294) (actual time=0.005..0.005 rows=2 loops=531)

17. 2.124 2.124 ↑ 1.0 3 531

Seq Scan on uc20_offer_matrix (cost=0.00..1.03 rows=3 width=294) (actual time=0.003..0.004 rows=3 loops=531)

18. 2.124 2.124 ↑ 1.0 2 531

Seq Scan on uc19_offer_matrix (cost=0.00..1.02 rows=2 width=294) (actual time=0.004..0.004 rows=2 loops=531)

19. 10.089 10.089 ↓ 1.1 25 531

Seq Scan on uc22_offer_matrix (cost=0.00..4.22 rows=22 width=43) (actual time=0.004..0.019 rows=25 loops=531)

20. 10.620 10.620 ↑ 1.0 28 531

Seq Scan on uc28_offer_matrix (cost=0.00..4.28 rows=28 width=43) (actual time=0.003..0.020 rows=28 loops=531)

21. 1.062 32,861.466 ↑ 206.0 1 531

GroupAggregate (cost=1,026,615.11..1,026,620.26 rows=206 width=53) (actual time=61.886..61.886 rows=1 loops=531)

  • Group Key: fu.msisdn, fu.event_id, fu.fulfill_channel_status
22. 0.327 32,860.404 ↑ 206.0 1 531

Sort (cost=1,026,615.11..1,026,615.62 rows=206 width=57) (actual time=61.884..61.884 rows=1 loops=531)

  • Sort Key: fu.msisdn, fu.event_id, fu.fulfill_channel_status
  • Sort Method: quicksort Memory: 25kB
23. 32,860.077 32,860.077 ↑ 206.0 1 1

Seq Scan on event_fulfill fu (cost=0.00..1,026,607.19 rows=206 width=57) (actual time=1,099.741..32,860.077 rows=1 loops=1)

  • Filter: (((fulfill_channel_status)::text <> 'SUCCESS'::text) AND ((fulfill_dttm)::date >= '2019-08-15'::date))
  • Rows Removed by Filter: 18445480