explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GbGRB

Settings
# exclusive inclusive rows x rows loops node
1. 0.049 4,099.119 ↓ 7.9 63 1

Hash Left Join (cost=107,332.64..138,222.37 rows=8 width=206) (actual time=3,845.058..4,099.119 rows=63 loops=1)

  • Hash Cond: ((se.scheduled_email_transmission_id = ""*VALUES*"".column1) AND (se.applicant_id = ""*VALUES*"".column2) AND (se.customer_id = ""*VALUES*"".column3))
2. 0.047 4,098.969 ↓ 7.9 63 1

Nested Loop Left Join (cost=107,323.68..138,213.23 rows=8 width=202) (actual time=3,844.944..4,098.969 rows=63 loops=1)

3. 0.085 4,098.922 ↓ 7.9 63 1

Hash Left Join (cost=107,323.25..138,162.36 rows=8 width=207) (actual time=3,844.936..4,098.922 rows=63 loops=1)

  • Hash Cond: ((se.cid = stle.cid) AND (se.scheduled_task_log_id = stle.scheduled_task_log_id) AND (se.property_id = stle.property_id))
  • Join Filter: ((se.cid = 12859) AND ((se.sent_on)::date >= '2019-12-30'::date) AND ((se.sent_on)::date <= '2020-01-05'::date) AND ((se.system_email_type_id = 102) OR ((se.system_email_type_id = 62) AND (se.scheduled_email_id IS NOT NULL))) AND (se.property_id = ANY ('{218060,218061,218062,218064,218065,218066,218067,218068,218070,218071,218099,218104,218105,252494,518395,536150,570963,843826,886853,908584}'::integer[])))
4. 0.044 4,098.756 ↓ 7.9 63 1

Nested Loop Left Join (cost=107,309.05..138,147.99 rows=8 width=183) (actual time=3,844.811..4,098.756 rows=63 loops=1)

5. 0.138 4,092.097 ↓ 7.9 63 1

Nested Loop Left Join (cost=107,308.48..138,110.81 rows=8 width=119) (actual time=3,844.756..4,092.097 rows=63 loops=1)

  • Filter: ((((se.applicant_id IS NOT NULL) OR (ser.id IS NOT NULL)) AND (se.customer_id IS NULL) AND (se.company_employee_id IS NULL)) OR ((ser.id IS NOT NULL) AND (ser.reference_number IS NOT NULL)))
  • Rows Removed by Filter: 71
6. 3,153.561 4,089.145 ↓ 16.8 134 1

Bitmap Heap Scan on system_emails se (cost=107,307.91..138,073.65 rows=8 width=88) (actual time=3,843.231..4,089.145 rows=134 loops=1)

  • Recheck Cond: ((property_id = ANY ('{218060,218061,218062,218064,218065,218066,218067,218068,218070,218071,218099,218104,218105,252494,518395,536150,570963,843826,886853,908584}'::integer[])) AND (cid = 12859))
  • Filter: (((sent_on)::date >= '2019-12-30'::date) AND ((sent_on)::date <= '2020-01-05'::date) AND ((system_email_type_id = 102) OR ((system_email_type_id = 62) AND (scheduled_email_id IS NOT NULL))))
  • Rows Removed by Filter: 576421
  • Heap Blocks: exact=368674
7. 46.439 935.584 ↓ 0.0 0 1

BitmapAnd (cost=107,307.91..107,307.91 rows=15,109 width=0) (actual time=935.584..935.584 rows=0 loops=1)

8. 109.808 109.808 ↑ 1.5 576,808 1

Bitmap Index Scan on idx_system_emails_property_id (cost=0.00..12,232.25 rows=878,245 width=0) (actual time=109.808..109.808 rows=576,808 loops=1)

  • Index Cond: (property_id = ANY ('{218060,218061,218062,218064,218065,218066,218067,218068,218070,218071,218099,218104,218105,252494,518395,536150,570963,843826,886853,908584}'::integer[]))
9. 779.337 779.337 ↑ 2.1 3,234,219 1

Bitmap Index Scan on idx_system_emails_cid (cost=0.00..95,075.40 rows=6,839,311 width=0) (actual time=779.337..779.337 rows=3,234,219 loops=1)

  • Index Cond: (cid = 12859)
10. 2.814 2.814 ↓ 0.0 0 134

Index Scan using idx_system_email_recipients_custom_system_email_id on system_email_recipients ser (cost=0.57..4.62 rows=1 width=39) (actual time=0.021..0.021 rows=0 loops=134)

  • Index Cond: ((cid = se.cid) AND (cid = 12859) AND (recipient_type_id = 1) AND (system_email_id = se.id))
11. 6.615 6.615 ↑ 1.0 1 63

Index Scan using idx_email_events_system_email_id on email_events ee (cost=0.57..4.62 rows=1 width=72) (actual time=0.105..0.105 rows=1 loops=63)

  • Index Cond: (system_email_id = se.id)
  • Filter: ((cid = 12859) AND (cid = se.cid))
12. 0.046 0.081 ↑ 1.0 160 1

Hash (cost=8.20..8.20 rows=160 width=44) (actual time=0.081..0.081 rows=160 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
13. 0.035 0.035 ↑ 1.0 160 1

Seq Scan on scheduled_task_logs_events stle (cost=0.00..8.20 rows=160 width=44) (actual time=0.008..0.035 rows=160 loops=1)

  • Filter: (cid = 12859)
14. 0.000 0.000 ↓ 0.0 0 63

Index Scan using idx_system_email_blocks_email_address on system_email_blocks seb (cost=0.43..6.33 rows=1 width=26) (actual time=0.000..0.000 rows=0 loops=63)

  • Index Cond: ((email_address)::text = ser.email_address)
  • Filter: ((cid = 12859) AND (email_block_type_id = 15) AND (cid = ser.cid) AND ((created_on)::date >= '2019-12-30'::date) AND ((created_on)::date <= '2020-01-05'::date))
15. 0.034 0.101 ↑ 1.0 128 1

Hash (cost=4.16..4.16 rows=128 width=16) (actual time=0.101..0.101 rows=128 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
16. 0.067 0.067 ↑ 1.0 128 1

Values Scan on ""*VALUES*"" (cost=0.00..4.16 rows=128 width=16) (actual time=0.006..0.067 rows=128 loops=1)

Planning time : 26.463 ms