explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xu2r

Settings
# exclusive inclusive rows x rows loops node
1. 0.739 27,786.677 ↑ 1.0 50 1

Nested Loop Left Join (cost=7,663.73..9,952.39 rows=50 width=1,488) (actual time=27,517.659..27,786.677 rows=50 loops=1)

2. 0.132 27,634.688 ↑ 1.0 50 1

Nested Loop (cost=7,663.31..8,086.14 rows=50 width=1,237) (actual time=27,456.804..27,634.688 rows=50 loops=1)

3. 0.116 27,420.206 ↑ 1.0 50 1

Limit (cost=7,662.89..7,663.01 rows=50 width=12) (actual time=27,420.036..27,420.206 rows=50 loops=1)

4. 229.075 27,420.090 ↑ 39.5 50 1

Sort (cost=7,662.89..7,667.83 rows=1,977 width=12) (actual time=27,420.033..27,420.090 rows=50 loops=1)

  • Sort Key: send_mail_email.created_at DESC
  • Sort Method: top-N heapsort Memory: 27kB
5. 27,058.320 27,191.015 ↓ 190.1 375,742 1

Bitmap Heap Scan on send_mail_email (cost=62.57..7,597.21 rows=1,977 width=12) (actual time=152.999..27,191.015 rows=375,742 loops=1)

  • Recheck Cond: ((app_id = ANY ('{1}'::integer[])) AND (status = ANY ('{1}'::integer[])) AND (date(timezone('UTC'::text, created_at)) >= '2018-12-16'::date) AND (date(timezone('UTC'::text, created_at)) <= '2019-03-16'::date))
  • Rows Removed by Index Recheck: 10579
  • Heap Blocks: exact=47352 lossy=26588
6. 132.695 132.695 ↓ 190.1 375,742 1

Bitmap Index Scan on send_mail_email_app_id_status_created_at_asdate (cost=0.00..62.08 rows=1,977 width=0) (actual time=132.695..132.695 rows=375,742 loops=1)

  • Index Cond: ((app_id = ANY ('{1}'::integer[])) AND (status = ANY ('{1}'::integer[])) AND (date(timezone('UTC'::text, created_at)) >= '2018-12-16'::date) AND (date(timezone('UTC'::text, created_at)) <= '2019-03-16'::date))
7. 214.350 214.350 ↑ 1.0 1 50

Index Scan using send_mail_email_pkey on send_mail_email info (cost=0.42..8.44 rows=1 width=1,233) (actual time=4.286..4.287 rows=1 loops=50)

  • Index Cond: (id = send_mail_email.id)
8. 69.400 69.400 ↑ 1.0 1 50

Index Scan using collector_stats_fc41f286 on collector_stats stats (cost=0.42..8.36 rows=1 width=255) (actual time=1.387..1.388 rows=1 loops=50)

  • Index Cond: (email_id = send_mail_email.id)
9.          

SubPlan (forNested Loop Left Join)

10. 0.150 6.050 ↑ 1.0 1 50

Result (cost=4.18..4.19 rows=1 width=0) (actual time=0.120..0.121 rows=1 loops=50)

11.          

Initplan (forResult)

12. 5.900 5.900 ↓ 0.0 0 50

Index Only Scan using track_linkemail_fdfd0ebf on track_linkemail (cost=0.15..8.22 rows=2 width=0) (actual time=0.118..0.118 rows=0 loops=50)

  • Index Cond: (email_id = info.id)
  • Heap Fetches: 0
13. 0.150 1.950 ↑ 1.0 1 50

Result (cost=8.17..8.18 rows=1 width=0) (actual time=0.038..0.039 rows=1 loops=50)

14.          

Initplan (forResult)

15. 1.800 1.800 ↓ 0.0 0 50

Index Only Scan using track_mailopen_fdfd0ebf on track_mailopen (cost=0.15..8.17 rows=1 width=0) (actual time=0.036..0.036 rows=0 loops=50)

  • Index Cond: (email_id = info.id)
  • Heap Fetches: 0
16. 53.500 53.500 ↑ 1.0 1 50

Index Scan using campaign_campaign_pkey on campaign_campaign (cost=0.28..8.29 rows=1 width=4) (actual time=1.070..1.070 rows=1 loops=50)

  • Index Cond: (id = info.campaign_id)
17. 20.350 20.350 ↓ 0.0 0 50

Index Scan using mail_template_template_pkey on mail_template_template (cost=0.27..8.29 rows=1 width=32) (actual time=0.407..0.407 rows=0 loops=50)

  • Index Cond: (id = info.template_id)