explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y7UI

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.050 16,659.304 ↓ 25.0 50 1

Limit (cost=171,873.02..171,873.02 rows=2 width=16) (actual time=16,659.235..16,659.304 rows=50 loops=1)

  • Output: mail.id, mail.template_id, mail.created_at
  • Buffers: shared hit=647 read=77251 written=140
2. 2.114 16,659.254 ↓ 25.0 50 1

Sort (cost=171,873.02..171,873.02 rows=2 width=16) (actual time=16,659.232..16,659.254 rows=50 loops=1)

  • Output: mail.id, mail.template_id, mail.created_at
  • Sort Key: mail.created_at
  • Sort Method: top-N heapsort Memory: 27kB
  • Buffers: shared hit=647 read=77251 written=140
3. 0.770 16,657.140 ↓ 58.0 116 1

Nested Loop (cost=8,218.80..171,873.01 rows=2 width=16) (actual time=270.566..16,657.140 rows=116 loops=1)

  • Output: mail.id, mail.template_id, mail.created_at
  • Buffers: shared hit=647 read=77251 written=140
4. 16,035.917 16,270.215 ↓ 14.4 187 1

Bitmap Heap Scan on public.send_mail_email mail (cost=8,218.38..171,762.74 rows=13 width=16) (actual time=247.726..16,270.215 rows=187 loops=1)

  • Output: mail.id, mail.template_id, mail.created_at
  • Recheck Cond: ((mail.app_id = 1) AND (date(timezone('UTC'::text, mail.created_at)) >= '2018-12-12'::date) AND (date(timezone('UTC'::text, mail.created_at)) <= '2019-03-11'::date))
  • Rows Removed by Index Recheck: 1980
  • Filter: ((NOT mail.from_system) AND (lower((mail.email)::text) ~~* '%thiago.decastro2@gmail.com%'::text))
  • Rows Removed by Filter: 413846
  • Heap Blocks: exact=49134 lossy=26456
  • Buffers: shared hit=95 read=77124 written=140
5. 234.298 234.298 ↓ 3.1 419,609 1

Bitmap Index Scan on send_mail_email_app_id_created_at_asdate_from_system (cost=0.00..8,218.38 rows=134,199 width=0) (actual time=234.298..234.298 rows=419,609 loops=1)

  • Index Cond: ((mail.app_id = 1) AND (date(timezone('UTC'::text, mail.created_at)) >= '2018-12-12'::date) AND (date(timezone('UTC'::text, mail.created_at)) <= '2019-03-11'::date) AND (mail.from_system = false))
  • Buffers: shared hit=1 read=1628
6. 386.155 386.155 ↑ 1.0 1 187

Index Scan using collector_stats_fc41f286 on public.collector_stats stats (cost=0.42..8.47 rows=1 width=4) (actual time=2.065..2.065 rows=1 loops=187)

  • Output: stats.id, stats.registry_at, stats.stats, stats.app_id, stats.email_id, stats.diagnostic_code, stats.notification_type, stats.reject_reason_subtype, stats.reject_reason_type, stats.sent_by, stats.aws_ses_date, stats.status_id
  • Index Cond: (stats.email_id = mail.id)
  • Filter: ((stats.status_id = 0) OR (stats.status_id = 1) OR (stats.status_id = 2) OR (stats.status_id = 3) OR (stats.status_id = 4) OR (stats.status_id = 5) OR (stats.status_id = 6) OR (stats.status_id = 7) OR (stats.status_id = 8) OR (s (...)
  • Buffers: shared hit=552 read=127