explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nxSF

Settings
# exclusive inclusive rows x rows loops node
1. 22,015.543 26,806.938 ↓ 1.5 306,652 1

Bitmap Heap Scan on public.send_mail_email mail (cost=19,511.32..6,387,907.91 rows=205,631 width=1,231) (actual time=1,542.626..26,806.938 rows=306,652 loops=1)

  • Output: mail.id, mail.app_id, mail.name_sender, mail.email_sender, mail.name_receiver, mail.email, mail.subject, mail.uuid, mail.content, mail.second_content, mail.created_at, mail.send_date, mail.campaign_id, mail.template_id, (SubPlan 2), (SubPlan 4), (...)
  • 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: 20302
  • Filter: ((NOT mail.from_system) AND ((mail.status = 2) OR (mail.status = 3) OR (mail.status = 4) OR (mail.status = 5)))
  • Rows Removed by Filter: 107382
  • Heap Blocks: exact=42081 lossy=52901
  • Buffers: shared hit=1533048 read=97574
2. 1,418.223 1,418.223 ↓ 1.5 497,367 1

Bitmap Index Scan on send_mail_email_app_id_created_at_asdate_from_system_status (cost=0.00..19,459.91 rows=321,832 width=0) (actual time=1,418.223..1,418.223 rows=497,367 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=2483
3.          

SubPlan (forBitmap Heap Scan)

4. 613.304 919.956 ↑ 1.0 1 306,652

Result (cost=4.18..4.19 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=306,652)

  • Output: $1
  • Buffers: shared hit=306662 read=3
5.          

Initplan (forResult)

6. 306.652 306.652 ↓ 0.0 0 306,652

Index Only Scan using track_linkemail_fdfd0ebf on public.track_linkemail (cost=0.15..8.22 rows=2 width=0) (actual time=0.001..0.001 rows=0 loops=306,652)

  • Index Cond: (track_linkemail.email_id = mail.id)
  • Heap Fetches: 13
  • Buffers: shared hit=306662 read=3
7. 613.304 919.956 ↑ 1.0 1 306,652

Result (cost=8.17..8.18 rows=1 width=0) (actual time=0.002..0.003 rows=1 loops=306,652)

  • Output: $3
  • Buffers: shared hit=306662 read=3
8.          

Initplan (forResult)

9. 306.652 306.652 ↓ 0.0 0 306,652

Index Only Scan using track_mailopen_fdfd0ebf on public.track_mailopen (cost=0.15..8.17 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=306,652)

  • Index Cond: (track_mailopen.email_id = mail.id)
  • Heap Fetches: 13
  • Buffers: shared hit=306662 read=3
10. 1,226.608 1,226.608 ↑ 1.0 1 306,652

Index Scan using campaign_campaign_pkey on public.campaign_campaign (cost=0.28..8.29 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=306,652)

  • Output: campaign_campaign.status
  • Index Cond: (campaign_campaign.id = mail.campaign_id)
  • Buffers: shared hit=919129 read=110
11. 306.652 306.652 ↓ 0.0 0 306,652

Index Scan using mail_template_template_pkey on public.mail_template_template (cost=0.27..8.29 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=306,652)

  • Output: row_to_json(ROW(mail_template_template.name, mail_template_template.slug))
  • Index Cond: (mail_template_template.id = mail.template_id)
  • Buffers: shared hit=581 read=7