explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DxW3

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.704 78.924 ↑ 1.8 26 1

GroupAggregate (cost=2,260.64..2,261.68 rows=46 width=28) (actual time=74.160..78.924 rows=26 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=2,734 read=6,692
2. 5.991 75.220 ↓ 260.4 11,978 1

Sort (cost=2,260.64..2,260.76 rows=46 width=6) (actual time=74.129..75.220 rows=11,978 loops=1)

  • Output: email_history.object_id, email_history.opened, email_history.clicked
  • Sort Key: email_history.object_id
  • Sort Method: quicksort Memory: 946kB
  • Buffers: shared hit=2,734 read=6,692
3. 6.429 69.229 ↓ 260.4 11,978 1

Nested Loop (cost=0.56..2,259.37 rows=46 width=6) (actual time=4.087..69.229 rows=11,978 loops=1)

  • Output: email_history.object_id, email_history.opened, email_history.clicked
  • Buffers: shared hit=2,731 read=6,692
4. 14.290 14.290 ↓ 1.2 42 1

Seq Scan on public.hub_auto_messages (cost=0.00..1,848.90 rows=36 width=4) (actual time=0.248..14.290 rows=42 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 = 92) OR hub_auto_messages.is_default)
  • Rows Removed by Filter: 4,384
  • Buffers: shared hit=1 read=1,794
5. 48.510 48.510 ↓ 142.5 285 42

Index Scan using email_history_object_type_id_hub_idx on public.email_history (cost=0.56..11.38 rows=2 width=6) (actual time=0.031..1.155 rows=285 loops=42)

  • 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 = 92))
  • Buffers: shared hit=2,730 read=4,898
Planning time : 2.615 ms
Execution time : 79.197 ms