explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mihz

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 18,374.839 ↑ 1.0 1 1

Aggregate (cost=771.86..771.87 rows=1 width=8) (actual time=18,374.839..18,374.839 rows=1 loops=1)

  • Buffers: shared hit=18,494,939 read=25, temp read=58,160 written=120,728
  • I/O Timings: read=47.646
2.          

CTE get_targeted_event_ids

3. 0.033 18,370.278 ↑ 4.8 160 1

Nested Loop (cost=0.77..40.92 rows=766 width=8) (actual time=18,328.180..18,370.278 rows=160 loops=1)

  • Buffers: shared hit=18,493,664 read=25, temp read=58,160 written=120,728
  • I/O Timings: read=47.646
4. 0.214 18,322.197 ↓ 39.0 39 1

HashAggregate (cost=0.34..0.35 rows=1 width=8) (actual time=18,322.183..18,322.197 rows=39 loops=1)

  • Group Key: retrieve_person_linked_recipients.recipient_id
  • Buffers: shared hit=18,493,551 read=15, temp read=58,160 written=120,728
  • I/O Timings: read=0.135
5. 18,321.983 18,321.983 ↓ 1,361.0 1,361 1

Function Scan on retrieve_person_linked_recipients (cost=0.03..0.34 rows=1 width=8) (actual time=18,321.770..18,321.983 rows=1,361 loops=1)

  • Filter: (org_id = 1)
  • Buffers: shared hit=18,493,551 read=15, temp read=58,160 written=120,728
  • I/O Timings: read=0.135
6. 48.048 48.048 ↑ 191.5 4 39

Index Only Scan using ev_targeted_recipients_pkey on ev_targeted_recipients etr (cost=0.43..32.91 rows=766 width=16) (actual time=1.231..1.232 rows=4 loops=39)

  • Index Cond: ((org_id = 1) AND (recipient_id = retrieve_person_linked_recipients.recipient_id))
  • Heap Fetches: 1
  • Buffers: shared hit=113 read=10
  • I/O Timings: read=47.511
7. 0.002 18,374.836 ↓ 0.0 0 1

Hash Left Join (cost=373.10..729.29 rows=658 width=8) (actual time=18,374.835..18,374.836 rows=0 loops=1)

  • Hash Cond: (runtimeeve0_.application_id = applicatio1_.application_id)
  • Filter: ((runtimeeve0_.application_id IS NULL) OR (applicatio1_.application_id IS NULL) OR ((applicatio1_.name)::text <> ALL ('{"Voice Recording Notifications","Error Response Notifications","User Upload Onboarding Notifications","Data Upload Notifications"}'::text[])))
  • Buffers: shared hit=18,494,939 read=25, temp read=58,160 written=120,728
  • I/O Timings: read=47.646
8. 1.283 18,374.834 ↓ 0.0 0 1

Index Scan using idx_evs_status on evs runtimeeve0_ (cost=369.62..723.66 rows=751 width=15) (actual time=18,374.834..18,374.834 rows=0 loops=1)

  • Index Cond: ((status)::text = ANY ('{SUPPRESSED,CREATE,PURGED,ACTIVE,SUSPENDED}'::text[]))
  • Filter: ((dmn_name IS NOT NULL) AND ((status)::text <> 'SUPPRESSED'::text) AND ((system_message_type IS NULL) OR ((system_message_type)::text = 'SYSTEM_MESSAGE'::text)) AND (company_id = 1) AND ((hashed SubPlan 2) OR (hashed SubPlan 3)))
  • Rows Removed by Filter: 837
  • Buffers: shared hit=18,494,939 read=25, temp read=58,160 written=120,728
  • I/O Timings: read=47.646
9.          

SubPlan (for Index Scan)

10. 3.236 3.236 ↓ 0.0 0 1

Index Scan using idx_evs_status on evs runtimeeve2_ (cost=0.42..351.95 rows=5 width=8) (actual time=3.236..3.236 rows=0 loops=1)

  • Index Cond: ((status)::text = ANY ('{SUPPRESSED,CREATE,PURGED,ACTIVE,SUSPENDED}'::text[]))
  • Filter: ((dmn_name IS NOT NULL) AND ((status)::text <> 'SUPPRESSED'::text) AND (company_id = 1) AND (lower((sender)::text) = '45067523'::text))
  • Rows Removed by Filter: 837
  • Buffers: shared hit=640
11. 18,370.315 18,370.315 ↑ 4.8 160 1

CTE Scan on get_targeted_event_ids (cost=0.00..15.32 rows=766 width=8) (actual time=18,328.183..18,370.315 rows=160 loops=1)

  • Buffers: shared hit=18,493,664 read=25, temp read=58,160 written=120,728
  • I/O Timings: read=47.646
12. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.66..2.66 rows=66 width=29) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Seq Scan on application applicatio1_ (cost=0.00..2.66 rows=66 width=29) (never executed)

Planning time : 1.018 ms
Execution time : 18,375.770 ms