explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rpOT : Optimization for: Slow query; plan #kETw

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.711 54,078.762 ↑ 1.0 1 1

Aggregate (cost=353,424.98..353,424.99 rows=1 width=8) (actual time=54,078.762..54,078.762 rows=1 loops=1)

2. 568.193 54,077.051 ↓ 427.0 26,471 1

Sort (cost=353,424.05..353,424.20 rows=62 width=1,436) (actual time=54,060.207..54,077.051 rows=26,471 loops=1)

  • Sort Key: sub.email
  • Sort Method: external merge Disk: 3272kB
3. 13.225 53,508.858 ↓ 427.0 26,471 1

GroupAggregate (cost=353,420.65..353,422.20 rows=62 width=1,436) (actual time=53,473.742..53,508.858 rows=26,471 loops=1)

  • Group Key: sub.subscriber_uuid, sub.email, sub.firstname, sub.lastname, sub.locale
4. 727.047 53,495.633 ↓ 664.8 41,218 1

Sort (cost=353,420.65..353,420.80 rows=62 width=702) (actual time=53,473.733..53,495.633 rows=41,218 loops=1)

  • Sort Key: sub.subscriber_uuid, sub.email, sub.firstname, sub.lastname, sub.locale
  • Sort Method: external merge Disk: 3800kB
5. 231.920 52,768.586 ↓ 664.8 41,218 1

Hash Join (cost=297,869.19..353,418.80 rows=62 width=702) (actual time=52,498.528..52,768.586 rows=41,218 loops=1)

  • Hash Cond: ((memberparticipation.member_uuid)::text = (sub.member_uuid)::text)
6. 251.390 251.390 ↓ 29.1 27,372 1

Seq Scan on memberparticipation (cost=0.00..50,097.79 rows=940 width=37) (actual time=211.958..251.390 rows=27,372 loops=1)

  • Filter: ((member_uuid IS NOT NULL) AND ((campaign_uuid)::text = '5e91743c-0261-488d-898d-44f12e5132a2'::text) AND (creationtime >= to_date('2019/02/20 00:00:00'::text, 'yyyy/mm/dd HH24:MI:SS'::text)) AND (creationtime <= to_date('2019/03/25 23:59:59'::text, 'yyyy/mm/dd HH24:MI:SS'::text)))
  • Rows Removed by Filter: 937330
7. 203.661 52,285.276 ↓ 18.3 457,312 1

Hash (cost=295,063.40..295,063.40 rows=25,023 width=792) (actual time=52,285.276..52,285.276 rows=457,312 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 32 (originally 8) Memory Usage: 4033kB
8. 78.461 52,081.615 ↓ 20.6 515,356 1

Subquery Scan on sub (cost=294,187.60..295,063.40 rows=25,023 width=792) (actual time=48,761.834..52,081.615 rows=515,356 loops=1)

9. 96.858 52,003.154 ↓ 20.6 515,356 1

Unique (cost=294,187.60..294,813.17 rows=25,023 width=1,914) (actual time=48,761.833..52,003.154 rows=515,356 loops=1)

10. 12,324.975 51,906.296 ↓ 20.6 515,356 1

Sort (cost=294,187.60..294,250.15 rows=25,023 width=1,914) (actual time=48,761.832..51,906.296 rows=515,356 loops=1)

  • Sort Key: por_mailinglistsubscription.uuid, por_mailinglist.name, lastevent.source, por_mailinglistmember.member_uuid, por_mailinglistmember.uuid, por_mailinglistmember.email, (CASE WHEN ((por_mailinglistmember.locale IS NOT NULL) AND ((por_mailinglistmember.locale)::text <> ''::text)) THEN por_mailinglistmember.locale WHEN ((por_mailinglistmember.locale IS NOT NULL) AND ((por_mailinglistmember.locale)::text <> ''::text)) THEN por_mailinglistmember.locale ELSE NULL::character varying END), (CASE WHEN ((por_mailinglistmember.firstname IS NOT NULL) AND ((por_mailinglistmember.firstname)::text <> ''::text)) THEN por_mailinglistmember.firstname WHEN ((por_mailinglistmember.firstname IS NOT NULL) AND ((por_mailinglistmember.firstname)::text <> ''::text)) THEN por_mailinglistmember.firstname ELSE por_mailinglistmember.email END), (CASE WHEN ((por_mailinglistmember.lastname IS NOT NULL) AND ((por_mailinglistmember.lastname)::text <> ''::text)) THEN por_mailinglistmember.lastname WHEN ((por_mailinglistmember.lastname IS NOT NULL) AND ((por_mailinglistmember.lastname)::text <> ''::text)) THEN por_mailinglistmember.lastname ELSE ''::character varying END)
  • Sort Method: external merge Disk: 93888kB
11. 57.385 39,581.321 ↓ 20.6 515,356 1

Append (cost=76,137.16..271,573.04 rows=25,023 width=1,914) (actual time=838.536..39,581.321 rows=515,356 loops=1)

12. 165.754 12,383.850 ↓ 13.6 58,044 1

Nested Loop (cost=76,137.16..128,193.24 rows=4,275 width=1,747) (actual time=838.536..12,383.850 rows=58,044 loops=1)

13. 151.761 1,910.976 ↓ 24.8 515,356 1

Hash Join (cost=76,136.74..117,793.93 rows=20,748 width=1,106) (actual time=826.791..1,910.976 rows=515,356 loops=1)

  • Hash Cond: ((por_mailinglistsubscription.mailinglist_uuid)::text = (por_mailinglist.uuid)::text)
14. 816.909 1,759.180 ↓ 10.8 673,234 1

Hash Join (cost=76,135.58..117,351.87 rows=62,244 width=627) (actual time=822.204..1,759.180 rows=673,234 loops=1)

  • Hash Cond: ((lastevent.subscription_uuid)::text = (por_mailinglistsubscription.uuid)::text)
15. 120.268 120.268 ↓ 10.8 673,234 1

Seq Scan on v_latest_subscription_events lastevent (cost=0.00..9,514.44 rows=62,244 width=606) (actual time=0.019..120.268 rows=673,234 loops=1)

16. 533.878 822.003 ↑ 1.0 1,279,359 1

Hash (cost=38,903.59..38,903.59 rows=1,279,359 width=111) (actual time=822.003..822.003 rows=1,279,359 loops=1)

  • Buckets: 32768 Batches: 64 Memory Usage: 3028kB
17. 288.125 288.125 ↑ 1.0 1,279,359 1

Seq Scan on por_mailinglistsubscription (cost=0.00..38,903.59 rows=1,279,359 width=111) (actual time=0.020..288.125 rows=1,279,359 loops=1)

18. 0.008 0.035 ↑ 1.0 3 1

Hash (cost=1.12..1.12 rows=3 width=606) (actual time=0.035..0.035 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.027 0.027 ↑ 1.0 3 1

Seq Scan on por_mailinglist (cost=0.00..1.12 rows=3 width=606) (actual time=0.025..0.027 rows=3 loops=1)

  • Filter: ((name)::text = ANY ('{ds.support,ds.promotion,ds.partner}'::text[]))
  • Rows Removed by Filter: 6
20. 10,307.120 10,307.120 ↓ 0.0 0 515,356

Index Scan using por_mailinglistmember_member_uuid_index on por_mailinglistmember (cost=0.42..0.49 rows=1 width=115) (actual time=0.020..0.020 rows=0 loops=515,356)

  • Index Cond: ((uuid)::text = (por_mailinglistsubscription.mailinglistuser_uuid)::text)
  • Filter: (member_uuid IS NULL)
  • Rows Removed by Filter: 1
21. 610.696 27,140.086 ↓ 22.0 457,312 1

Nested Loop (cost=76,137.59..143,129.58 rows=20,748 width=1,263) (actual time=992.619..27,140.086 rows=457,312 loops=1)

22. 125.225 14,676.202 ↓ 24.8 515,356 1

Nested Loop (cost=76,137.16..128,129.11 rows=20,748 width=1,167) (actual time=992.561..14,676.202 rows=515,356 loops=1)

23. 195.554 2,182.433 ↓ 24.8 515,356 1

Hash Join (cost=76,136.74..117,793.93 rows=20,748 width=1,106) (actual time=992.523..2,182.433 rows=515,356 loops=1)

  • Hash Cond: ((por_mailinglistsubscription_1.mailinglist_uuid)::text = (por_mailinglist_1.uuid)::text)
24. 876.673 1,986.861 ↓ 10.8 673,234 1

Hash Join (cost=76,135.58..117,351.87 rows=62,244 width=627) (actual time=987.738..1,986.861 rows=673,234 loops=1)

  • Hash Cond: ((lastevent_1.subscription_uuid)::text = (por_mailinglistsubscription_1.uuid)::text)
25. 122.606 122.606 ↓ 10.8 673,234 1

Seq Scan on v_latest_subscription_events lastevent_1 (cost=0.00..9,514.44 rows=62,244 width=606) (actual time=0.020..122.606 rows=673,234 loops=1)

26. 701.864 987.582 ↑ 1.0 1,279,359 1

Hash (cost=38,903.59..38,903.59 rows=1,279,359 width=111) (actual time=987.582..987.582 rows=1,279,359 loops=1)

  • Buckets: 32768 Batches: 64 Memory Usage: 3028kB
27. 285.718 285.718 ↑ 1.0 1,279,359 1

Seq Scan on por_mailinglistsubscription por_mailinglistsubscription_1 (cost=0.00..38,903.59 rows=1,279,359 width=111) (actual time=0.015..285.718 rows=1,279,359 loops=1)

28. 0.002 0.018 ↑ 1.0 3 1

Hash (cost=1.12..1.12 rows=3 width=606) (actual time=0.018..0.018 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.016 0.016 ↑ 1.0 3 1

Seq Scan on por_mailinglist por_mailinglist_1 (cost=0.00..1.12 rows=3 width=606) (actual time=0.014..0.016 rows=3 loops=1)

  • Filter: ((name)::text = ANY ('{ds.support,ds.promotion,ds.partner}'::text[]))
  • Rows Removed by Filter: 6
30. 12,368.544 12,368.544 ↑ 1.0 1 515,356

Index Scan using por_mailinglistmember_member_uuid_index on por_mailinglistmember por_mailinglistmember_1 (cost=0.42..0.49 rows=1 width=98) (actual time=0.024..0.024 rows=1 loops=515,356)

  • Index Cond: ((uuid)::text = (por_mailinglistsubscription_1.mailinglistuser_uuid)::text)
31. 11,853.188 11,853.188 ↑ 1.0 1 515,356

Index Scan using por_member_uuid_index on por_member (cost=0.42..0.70 rows=1 width=81) (actual time=0.023..0.023 rows=1 loops=515,356)

  • Index Cond: ((uuid)::text = (por_mailinglistmember_1.member_uuid)::text)