explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B0Y8 : record eligibility

Settings
# exclusive inclusive rows x rows loops node
1. 0.067 4,661.413 ↑ 1.0 1,000 1

Limit (cost=517,291.92..517,294.42 rows=1,000 width=626) (actual time=4,661.231..4,661.413 rows=1,000 loops=1)

2.          

CTE ith

3. 0.015 0.015 ↓ 0.0 0 1

Index Scan using ixthreadhistory_recordhistorycover_index on interaction_thread_history (cost=0.29..2.33 rows=1 width=89) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: ((created_date_time >= '2020-10-01 00:00:00'::timestamp without time zone) AND (created_date_time <= '2020-10-14 00:00:00'::timestamp without time zone))
4. 168.752 4,661.346 ↑ 314.2 1,000 1

Sort (cost=517,289.60..518,075.16 rows=314,227 width=626) (actual time=4,661.230..4,661.346 rows=1,000 loops=1)

  • Sort Key: (CASE WHEN (imh.id IS NULL) THEN history.start_date_time ELSE (ith.created_date_time)::timestamp with time zone END) DESC
  • Sort Method: top-N heapsort Memory: 1,102kB
5. 260.935 4,492.594 ↑ 1.3 248,016 1

Hash Left Join (cost=296,828.65..500,060.87 rows=314,227 width=626) (actual time=2,380.515..4,492.594 rows=248,016 loops=1)

  • Hash Cond: (CASE WHEN (imh.id IS NULL) THEN history.result_id ELSE imh.result_code_id END = rc.result_code_id)
6. 303.009 4,231.619 ↑ 1.3 248,016 1

Nested Loop Left Join (cost=296,821.82..488,967.92 rows=314,227 width=1,240) (actual time=2,380.450..4,231.619 rows=248,016 loops=1)

  • Join Filter: ((CASE WHEN (imh.id IS NULL) THEN NULL::integer ELSE ith.channel_id END = ic.id) OR ((CASE WHEN (imh.id IS NULL) THEN NULL::integer ELSE ith.channel_id END IS NULL) AND (ic.channel_type = 0)))
  • Rows Removed by Join Filter: 744,048
7. 132.477 3,928.610 ↑ 1.3 248,016 1

Hash Left Join (cost=296,821.82..441,830.44 rows=314,227 width=1,223) (actual time=2,380.436..3,928.610 rows=248,016 loops=1)

  • Hash Cond: (CASE WHEN (imh.id IS NULL) THEN history.list_id ELSE ith.list_id END = l.list_id)
8. 123.024 3,795.933 ↑ 1.3 248,016 1

Hash Left Join (cost=296,800.42..440,935.30 rows=314,227 width=656) (actual time=2,380.219..3,795.933 rows=248,016 loops=1)

  • Hash Cond: (CASE WHEN (imh.id IS NULL) THEN history.campaign_id ELSE ith.campaign_id END = c.campaign_id)
9. 110.444 3,672.896 ↑ 1.3 248,016 1

Hash Left Join (cost=296,798.45..440,059.58 rows=314,227 width=641) (actual time=2,380.187..3,672.896 rows=248,016 loops=1)

  • Hash Cond: (CASE WHEN (imh.id IS NULL) THEN history.team_id ELSE ith.team_id END = t.team_id)
10. 111.194 3,562.435 ↑ 1.3 248,016 1

Hash Left Join (cost=296,795.66..439,183.05 rows=314,227 width=630) (actual time=2,380.147..3,562.435 rows=248,016 loops=1)

  • Hash Cond: (CASE WHEN (imh.id IS NULL) THEN history.user_id ELSE ith.user_id END = u.user_id)
11. 880.339 3,451.110 ↑ 1.3 248,016 1

Hash Left Join (cost=296,769.80..438,296.52 rows=314,227 width=617) (actual time=2,379.994..3,451.110 rows=248,016 loops=1)

  • Hash Cond: (CASE WHEN (imh.id IS NULL) THEN history.history_id ELSE COALESCE(history.history_id, (ith.id)::bigint) END = r.history_id)
  • Join Filter: ((CASE WHEN (imh.id IS NULL) THEN history.type ELSE COALESCE(history.type, CASE WHEN (imh.channel_type = 1) THEN 14 WHEN (imh.channel_type = 2) THEN 15 WHEN (imh.channel_type = 3) THEN 16 ELSE 5 END) END <> 10) AND (CASE WHEN (imh.id IS NULL) THEN history.type ELSE COALESCE(history.type, CASE WHEN (imh.channel_type = 1) THEN 14 WHEN (imh.channel_type = 2) THEN 15 WHEN (imh.channel_type = 3) THEN 16 ELSE 5 END) END <> 14) AND (CASE WHEN (imh.id IS NULL) THEN history.type ELSE COALESCE(history.type, CASE WHEN (imh.channel_type = 1) THEN 14 WHEN (imh.channel_type = 2) THEN 15 WHEN (imh.channel_type = 3) THEN 16 ELSE 5 END) END <> 15))
12. 229.969 1,063.171 ↑ 1.3 248,016 1

Hash Full Join (cost=58,616.46..104,782.46 rows=314,227 width=440) (actual time=870.411..1,063.171 rows=248,016 loops=1)

  • Hash Cond: (imh.call_history_id = history.history_id)
  • Filter: ((ith.id IS NOT NULL) OR ((history.history_id IS NOT NULL) AND (imh.id IS NULL)))
  • Rows Removed by Filter: 315,806
13. 67.288 382.339 ↑ 1.0 315,806 1

Hash Left Join (cost=22,206.85..46,621.56 rows=315,806 width=184) (actual time=94.100..382.339 rows=315,806 loops=1)

  • Hash Cond: (im.id = ith.last_message_id)
14. 175.512 315.017 ↑ 1.0 315,806 1

Hash Left Join (cost=22,187.75..45,418.15 rows=315,806 width=50) (actual time=94.039..315.017 rows=315,806 loops=1)

  • Hash Cond: (imh.message_id = im.id)
15. 47.168 47.168 ↑ 1.0 315,806 1

Seq Scan on interaction_message_history imh (cost=0.00..22,401.38 rows=315,806 width=50) (actual time=0.005..47.168 rows=315,806 loops=1)

16. 51.704 92.337 ↑ 1.0 315,827 1

Hash (cost=11,923.37..11,923.37 rows=315,827 width=4) (actual time=92.337..92.337 rows=315,827 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 15,200kB
17. 40.633 40.633 ↑ 1.0 315,827 1

Index Only Scan using pk_interaction_message on interaction_message im (cost=0.42..11,923.37 rows=315,827 width=4) (actual time=0.033..40.633 rows=315,827 loops=1)

  • Heap Fetches: 0
18. 0.000 0.034 ↓ 0.0 0 1

Hash (cost=19.08..19.08 rows=1 width=138) (actual time=0.034..0.034 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
19. 0.001 0.034 ↓ 0.0 0 1

Nested Loop Left Join (cost=15.73..19.08 rows=1 width=138) (actual time=0.034..0.034 rows=0 loops=1)

20. 0.016 0.033 ↓ 0.0 0 1

Hash Right Join (cost=15.45..16.75 rows=1 width=113) (actual time=0.033..0.033 rows=0 loops=1)

  • Hash Cond: (im2.thread_history_id = ith.id)
21. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=15.36..15.96 rows=20 width=40) (never executed)

  • Group Key: im2.thread_history_id
22. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.84..15.16 rows=20 width=10) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..4.56 rows=20 width=12) (never executed)

24. 0.000 0.000 ↓ 0.0 0

CTE Scan on ith ith_1 (cost=0.00..0.06 rows=1 width=4) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Index Scan using ixmessage_threadhistoryid_index on interaction_message im2 (cost=0.42..3.90 rows=20 width=12) (never executed)

  • Index Cond: (thread_history_id = ith_1.id)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using interactionmessagehistory_messageid_index on interaction_message_history imh2 (cost=0.42..0.50 rows=1 width=6) (never executed)

  • Index Cond: (message_id = im2.id)
27. 0.000 0.017 ↓ 0.0 0 1

Hash (cost=0.06..0.06 rows=1 width=89) (actual time=0.017..0.017 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
28. 0.017 0.017 ↓ 0.0 0 1

CTE Scan on ith (cost=0.00..0.06 rows=1 width=89) (actual time=0.017..0.017 rows=0 loops=1)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_interaction_thread on interaction_thread it (cost=0.29..2.32 rows=1 width=29) (never executed)

  • Index Cond: (id = ith.thread_id)
30. 187.674 450.863 ↓ 1.2 248,016 1

Hash (cost=23,257.64..23,257.64 rows=205,042 width=264) (actual time=450.863..450.863 rows=248,016 loops=1)

  • Buckets: 262,144 Batches: 2 Memory Usage: 33,412kB
31. 263.189 263.189 ↓ 1.2 248,016 1

Index Scan using history_startdatetime_index on history (cost=0.43..23,257.64 rows=205,042 width=264) (actual time=0.027..263.189 rows=248,016 loops=1)

  • Index Cond: ((start_date_time >= '2020-10-01 00:00:00+01'::timestamp with time zone) AND (start_date_time <= '2020-10-14 00:00:00+01'::timestamp with time zone))
  • Filter: (type <> 8)
  • Rows Removed by Filter: 8,300
32. 837.522 1,507.600 ↓ 1.0 1,788,391 1

Hash (cost=138,380.43..138,380.43 rows=1,774,351 width=185) (actual time=1,507.600..1,507.600 rows=1,788,391 loops=1)

  • Buckets: 524,288 Batches: 8 Memory Usage: 52,496kB
33. 670.078 670.078 ↓ 1.0 1,788,391 1

Seq Scan on recording r (cost=0.00..138,380.43 rows=1,774,351 width=185) (actual time=0.012..670.078 rows=1,788,391 loops=1)

34. 0.057 0.131 ↑ 1.0 273 1

Hash (cost=16.99..16.99 rows=273 width=17) (actual time=0.131..0.131 rows=273 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
35. 0.074 0.074 ↑ 1.0 273 1

Seq Scan on users u (cost=0.00..16.99 rows=273 width=17) (actual time=0.010..0.074 rows=273 loops=1)

36. 0.006 0.017 ↑ 1.0 27 1

Hash (cost=1.91..1.91 rows=27 width=15) (actual time=0.017..0.017 rows=27 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
37. 0.011 0.011 ↑ 1.0 27 1

Seq Scan on team t (cost=0.00..1.91 rows=27 width=15) (actual time=0.007..0.011 rows=27 loops=1)

38. 0.004 0.013 ↑ 1.0 14 1

Hash (cost=1.52..1.52 rows=14 width=19) (actual time=0.013..0.013 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
39. 0.009 0.009 ↑ 1.0 14 1

Seq Scan on campaign c (cost=0.00..1.52 rows=14 width=19) (actual time=0.008..0.009 rows=14 loops=1)

40. 0.047 0.200 ↑ 1.0 96 1

Hash (cost=18.28..18.28 rows=96 width=571) (actual time=0.200..0.200 rows=96 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 58kB
41. 0.153 0.153 ↑ 1.0 96 1

Seq Scan on list l (cost=0.00..18.28 rows=96 width=571) (actual time=0.024..0.153 rows=96 loops=1)

42. 0.000 0.000 ↑ 1.0 4 248,016

Materialize (cost=0.00..3.44 rows=4 width=29) (actual time=0.000..0.000 rows=4 loops=248,016)

43. 0.009 0.009 ↑ 1.0 4 1

Seq Scan on interaction_channel ic (cost=0.00..3.42 rows=4 width=29) (actual time=0.006..0.009 rows=4 loops=1)

44. 0.019 0.040 ↑ 1.0 74 1

Hash (cost=4.42..4.42 rows=74 width=23) (actual time=0.040..0.040 rows=74 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
45. 0.021 0.021 ↑ 1.0 74 1

Seq Scan on result_code rc (cost=0.00..4.42 rows=74 width=23) (actual time=0.010..0.021 rows=74 loops=1)

Planning time : 11.541 ms
Execution time : 4,663.115 ms