explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OvU5 : 22222

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 23.090 308.731 ↑ 4.4 38 1

GroupAggregate (cost=2,688.04..2,691.84 rows=169 width=28) (actual time=272.524..308.731 rows=38 loops=1)

  • Output: email_history.object_id, count(*), sum(CASE WHEN email_history.opened THEN 1 ELSE 0 END), sum(CASE WHEN email_history.clicked THEN 1 ELSE 0 END)
  • Group Key: email_history.object_id
  • Buffers: shared hit=21,809 read=22,712, temp read=148 written=149
2. 48.644 285.641 ↓ 447.3 75,600 1

Sort (cost=2,688.04..2,688.46 rows=169 width=6) (actual time=272.322..285.641 rows=75,600 loops=1)

  • Output: email_history.object_id, email_history.opened, email_history.clicked
  • Sort Key: email_history.object_id
  • Sort Method: external merge Disk: 1,184kB
  • Buffers: shared hit=21,809 read=22,712, temp read=148 written=149
3. 37.936 236.997 ↓ 447.3 75,600 1

Nested Loop (cost=0.56..2,681.78 rows=169 width=6) (actual time=1.251..236.997 rows=75,600 loops=1)

  • Output: email_history.object_id, email_history.opened, email_history.clicked
  • Buffers: shared hit=21,806 read=22,712
4. 7.925 7.925 ↓ 1.1 48 1

Seq Scan on public.hub_auto_messages (cost=0.00..1,848.90 rows=42 width=4) (actual time=0.174..7.925 rows=48 loops=1)

  • Output: hub_auto_messages.id, hub_auto_messages.hub_id, hub_auto_messages.owner_id, hub_auto_messages.to_conditions, hub_auto_messages.is_active, hub_auto_messages.created_at, hub_auto_messages.updated_at, hub_auto_messages.message_template, hub_auto_messages.name, hub_auto_messages.message_subject, hub_auto_messages.associated_survey, hub_auto_messages.cluster_id, hub_auto_messages.default_header_footer, hub_auto_messages.is_default, hub_auto_messages.identifier
  • Filter: ((hub_auto_messages.hub_id = 39) OR hub_auto_messages.is_default)
  • Rows Removed by Filter: 4,378
  • Buffers: shared hit=925 read=870
5. 191.136 191.136 ↓ 315.0 1,575 48

Index Scan using email_history_object_type_id_hub_idx on public.email_history (cost=0.56..19.78 rows=5 width=6) (actual time=0.033..3.982 rows=1,575 loops=48)

  • Output: email_history.id, email_history.user_id, email_history.email_id, email_history.subject, email_history.body, email_history.added_by, email_history.created_at, email_history.updated_at, email_history.object_id, email_history.postmark_id, email_history.opened, email_history.opened_time, email_history.open_data, email_history.clicked_time, email_history.clicked, email_history.click_data, email_history.pre_send_hash, email_history.object_type, email_history.sent_by_service, email_history.sendgrid_id, email_history.scheduled_time, email_history.latest_status_time, email_history.latest_status, email_history.latest_status_data, email_history.sent_object, email_history.view_id, email_history.hub_id, email_history.click_count, email_history.open_count
  • Index Cond: ((email_history.object_type = 'HubAutoMessage'::text) AND (email_history.object_id = hub_auto_messages.id) AND (email_history.hub_id = 39))
  • Buffers: shared hit=20,881 read=21,842
Planning time : 2.049 ms
Execution time : 309.984 ms