explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VVeC

Settings
# exclusive inclusive rows x rows loops node
1. 0.054 16,634.652 ↓ 25.0 50 1

Limit (cost=171,852.38..171,852.39 rows=2 width=16) (actual time=16,634.579..16,634.652 rows=50 loops=1)

  • Output: mail.id, mail.template_id, mail.created_at
  • Buffers: shared hit=570 read=77328
2. 0.298 16,634.598 ↓ 25.0 50 1

Sort (cost=171,852.38..171,852.39 rows=2 width=16) (actual time=16,634.576..16,634.598 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=570 read=77328
3. 1.636 16,634.300 ↓ 58.0 116 1

Nested Loop (cost=8,214.40..171,852.37 rows=2 width=16) (actual time=336.915..16,634.300 rows=116 loops=1)

  • Output: mail.id, mail.template_id, mail.created_at
  • Buffers: shared hit=570 read=77328
4. 15,967.072 16,256.420 ↓ 14.4 187 1

Bitmap Heap Scan on public.send_mail_email mail (cost=8,213.98..171,742.10 rows=13 width=16) (actual time=303.613..16,256.420 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=19 read=77200
5. 289.348 289.348 ↓ 3.1 419,609 1

Bitmap Index Scan on send_mail_email_app_id_created_at_asdate_from_system (cost=0.00..8,213.97 rows=134,185 width=0) (actual time=289.348..289.348 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 read=1629
6. 376.244 376.244 ↑ 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.012..2.012 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=551 read=128