explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Ydd

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 56.507 ↑ 1.0 1 1

Aggregate (cost=70,156.28..70,156.29 rows=1 width=40) (actual time=56.507..56.507 rows=1 loops=1)

2. 0.000 56.487 ↓ 0.0 0 1

Nested Loop Left Join (cost=40,867.63..70,071.96 rows=6,746 width=28) (actual time=56.487..56.487 rows=0 loops=1)

  • Join Filter: ((ahoy_messages.sent_at > '2019-07-09 00:00:00'::timestamp without time zone) AND (ahoy_messages.sent_at < '2019-07-09 23:59:59'::timestamp without time zone))
3. 0.002 56.487 ↓ 0.0 0 1

Merge Left Join (cost=40,867.21..40,925.55 rows=6,746 width=32) (actual time=56.487..56.487 rows=0 loops=1)

  • Merge Cond: ((ahoy_messages.mail_filter_id = clicked_links.mail_filter_id) AND (ahoy_messages.user_id = clicked_links.person_id))
  • Join Filter: ((ahoy_messages.sent_at > '2019-07-09 00:00:00'::timestamp without time zone) AND (ahoy_messages.sent_at < '2019-07-09 23:59:59'::timestamp without time zone))
4. 0.006 56.485 ↓ 0.0 0 1

Sort (cost=30,662.21..30,679.08 rows=6,746 width=20) (actual time=56.485..56.485 rows=0 loops=1)

  • Sort Key: ahoy_messages.mail_filter_id, ahoy_messages.user_id
  • Sort Method: quicksort Memory: 25kB
5. 0.010 56.479 ↓ 0.0 0 1

Nested Loop (cost=3,898.43..30,233.17 rows=6,746 width=20) (actual time=56.479..56.479 rows=0 loops=1)

6. 0.033 56.317 ↑ 54.2 19 1

HashAggregate (cost=3,897.87..3,908.16 rows=1,029 width=4) (actual time=56.304..56.317 rows=19 loops=1)

  • Group Key: mail_filters.id
7. 0.050 56.284 ↑ 54.2 19 1

Nested Loop (cost=0.41..3,895.30 rows=1,029 width=4) (actual time=1.022..56.284 rows=19 loops=1)

8. 56.030 56.030 ↑ 1.0 34 1

Seq Scan on projects (cost=0.00..3,177.14 rows=34 width=4) (actual time=0.033..56.030 rows=34 loops=1)

  • Filter: (ip_pool_name IS NULL)
  • Rows Removed by Filter: 12,481
9. 0.204 0.204 ↑ 30.0 1 34

Index Only Scan using composite_index_for_unsubscribed_percentage on mail_filters (cost=0.41..20.82 rows=30 width=8) (actual time=0.005..0.006 rows=1 loops=34)

  • Index Cond: (project_id = projects.id)
  • Heap Fetches: 0
10. 0.152 0.152 ↓ 0.0 0 19

Index Scan using index_ahoy_messages_on_mail_filter_id_and_sent_at on ahoy_messages (cost=0.56..25.51 rows=7 width=20) (actual time=0.008..0.008 rows=0 loops=19)

  • Index Cond: ((mail_filter_id = mail_filters.id) AND (sent_at > '2019-07-09 00:00:00'::timestamp without time zone) AND (sent_at < '2019-07-09 23:59:59'::timestamp without time zone))
11. 0.000 0.000 ↓ 0.0 0

Sort (cost=10,205.00..10,208.77 rows=1,510 width=12) (never executed)

  • Sort Key: clicked_links.mail_filter_id, clicked_links.person_id
12. 0.000 0.000 ↓ 0.0 0

Seq Scan on clicked_links (cost=0.00..10,125.26 rows=1,510 width=12) (never executed)

  • Filter: ((created_at > '2019-07-09 00:00:00'::timestamp without time zone) AND (created_at < '2019-07-10 23:59:59'::timestamp without time zone))
13. 0.000 0.000 ↓ 0.0 0

Index Scan using index_person_email_opens_on_person_id on person_email_opens (cost=0.43..4.31 rows=1 width=12) (never executed)

  • Index Cond: (person_id = ahoy_messages.user_id)
  • Filter: ((created_at > '2019-07-09 00:00:00'::timestamp without time zone) AND (created_at < '2019-07-10 23:59:59'::timestamp without time zone) AND (mail_filter_id = ahoy_messages.mail_filter_id))
Planning time : 3.542 ms
Execution time : 56.613 ms