explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DCr2

Settings
# exclusive inclusive rows x rows loops node
1. 189.133 243.967 ↓ 16,680.3 50,041 1

Hash Join (cost=1,036.88..5,568.41 rows=3 width=20) (actual time=2.909..243.967 rows=50,041 loops=1)

  • Hash Cond: ((campaigns.id = schedule_weekly.campaign_id) AND (date_part('dow'::text, ((timezone(texts.time_zone, now()))::date)::timestamp without time zone) = (schedule_weekly.dow)::double precision))
  • Join Filter: ((now() >= (timezone(texts.time_zone, ((timezone(texts.time_zone, now()))::date)::timestamp without time zone) + (schedule_weekly.start_time)::interval)) AND (now() < (timezone(texts.time_zone, ((timezone(texts.time_zone, now()))::date)::timestamp without time zone) + ((schedule_weekly.start_time + ('00:01:00'::interval * (schedule_weekly.duration)::double precision)))::interval)))
2.          

CTE t_camp_cur

3. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=17.07..17.08 rows=1 width=16) (never executed)

  • Group Key: campaigns_1.id, campaigns_1.customer_id
4. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.99..17.06 rows=1 width=16) (never executed)

5. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..16.89 rows=1 width=8) (never executed)

6. 0.000 0.000 ↓ 0.0 0

Index Scan using text_dialogs_texted_on_idx on text_dialogs (cost=0.43..8.45 rows=1 width=8) (never executed)

  • Index Cond: (texted_on > (now() - '08:20:00'::interval))
7. 0.000 0.000 ↓ 0.0 0

Index Scan using texts_pkey on texts texts_1 (cost=0.42..8.44 rows=1 width=16) (never executed)

  • Index Cond: (id = text_dialogs.text_id)
8. 0.000 0.000 ↓ 0.0 0

Index Scan using campaigns_pkey on campaigns campaigns_1 (cost=0.14..0.16 rows=1 width=16) (never executed)

  • Index Cond: (id = texts_1.campaign_id)
9.          

CTE t_serv_cur

10. 0.000 0.030 ↓ 0.0 0 1

GroupAggregate (cost=17.20..17.23 rows=1 width=25) (actual time=0.030..0.030 rows=0 loops=1)

  • Group Key: text_services.id
11. 0.004 0.030 ↓ 0.0 0 1

Sort (cost=17.20..17.21 rows=1 width=25) (actual time=0.030..0.030 rows=0 loops=1)

  • Sort Key: text_services.id
  • Sort Method: quicksort Memory: 25kB
12. 0.000 0.026 ↓ 0.0 0 1

Nested Loop (cost=1.12..17.19 rows=1 width=25) (actual time=0.026..0.026 rows=0 loops=1)

13. 0.003 0.026 ↓ 0.0 0 1

Nested Loop (cost=0.84..16.89 rows=1 width=16) (actual time=0.026..0.026 rows=0 loops=1)

14. 0.013 0.013 ↓ 10.0 10 1

Index Scan using text_dialogs_texted_on_idx on text_dialogs text_dialogs_1 (cost=0.43..8.45 rows=1 width=8) (actual time=0.008..0.013 rows=10 loops=1)

  • Index Cond: (texted_on > (now() - '24:00:00'::interval))
15. 0.010 0.010 ↓ 0.0 0 10

Index Scan using texts_pkey on texts texts_2 (cost=0.42..8.44 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (id = text_dialogs_1.text_id)
16. 0.000 0.000 ↓ 0.0 0

Index Scan using text_services_pkey on text_services (cost=0.28..0.29 rows=1 width=17) (never executed)

  • Index Cond: (id = texts_2.text_service_id)
17. 20.372 54.507 ↓ 60.1 50,041 1

Hash Join (cost=972.00..5,439.25 rows=833 width=39) (actual time=2.553..54.507 rows=50,041 loops=1)

  • Hash Cond: (texts.campaign_id = campaigns.id)
  • Join Filter: ((texts.count <= campaigns.text_dialog_limit) AND (texts.mismatch_count <= campaigns.text_mismatch_limit))
18. 11.499 34.063 ↓ 6.7 50,041 1

Hash Left Join (cost=959.10..5,285.72 rows=7,500 width=39) (actual time=2.476..34.063 rows=50,041 loops=1)

  • Hash Cond: (texts.text_service_id = t_serv_cur.text_service_id)
  • Filter: ((t_serv_cur.text_service_id IS NULL) OR (t_serv_cur.enabled AND (t_serv_cur.state = 1) AND (COALESCE(t_serv_cur.serv_texts_cur, '0'::bigint) < t_serv_cur.texts_max)))
19. 20.226 22.532 ↓ 1.1 50,041 1

Bitmap Heap Scan on texts (cost=959.07..5,120.65 rows=43,902 width=39) (actual time=2.441..22.532 rows=50,041 loops=1)

  • Recheck Cond: ((state = 2) OR (state = 3))
  • Filter: (enabled AND ((text_on IS NULL) OR (text_on <= now())) AND ((contact_lookup = 0) OR ((contact_lookup = 1) AND (contact_type >= 20))))
  • Heap Blocks: exact=1419
20. 0.000 2.306 ↓ 0.0 0 1

BitmapOr (cost=959.07..959.07 rows=50,203 width=0) (actual time=2.306..2.306 rows=0 loops=1)

21. 1.187 1.187 ↑ 1.0 25,079 1

Bitmap Index Scan on texts_state_idx (cost=0.00..468.82 rows=25,137 width=0) (actual time=1.187..1.187 rows=25,079 loops=1)

  • Index Cond: (state = 2)
22. 1.119 1.119 ↑ 1.0 24,962 1

Bitmap Index Scan on texts_state_idx (cost=0.00..468.30 rows=25,067 width=0) (actual time=1.119..1.119 rows=24,962 loops=1)

  • Index Cond: (state = 3)
23. 0.001 0.032 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=25) (actual time=0.032..0.032 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
24. 0.031 0.031 ↓ 0.0 0 1

CTE Scan on t_serv_cur (cost=0.00..0.02 rows=1 width=25) (actual time=0.031..0.031 rows=0 loops=1)

25. 0.019 0.072 ↑ 1.0 129 1

Hash (cost=11.29..11.29 rows=129 width=16) (actual time=0.072..0.072 rows=129 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
26. 0.053 0.053 ↑ 1.0 129 1

Seq Scan on campaigns (cost=0.00..11.29 rows=129 width=16) (actual time=0.004..0.053 rows=129 loops=1)

27. 0.198 0.327 ↑ 1.0 903 1

Hash (cost=17.03..17.03 rows=903 width=24) (actual time=0.327..0.327 rows=903 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 61kB
28. 0.129 0.129 ↑ 1.0 903 1

Seq Scan on schedule_weekly (cost=0.00..17.03 rows=903 width=24) (actual time=0.002..0.129 rows=903 loops=1)

  • Filter: enabled
Planning time : 1.258 ms
Execution time : 247.134 ms