explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wuJX

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 2.760 ↓ 5.0 10 1

Limit (cost=2,002.12..2,002.13 rows=2 width=3,747) (actual time=2.757..2.760 rows=10 loops=1)

2.          

CTE room_logs_raw

3. 0.076 0.126 ↓ 1.2 162 1

Seq Scan on room_logs r_2 (cost=5.34..14.16 rows=140 width=838) (actual time=0.010..0.126 rows=162 loops=1)

  • Filter: ((NOT has_no_message) AND (app_id = 1))
  • Rows Removed by Filter: 26
4.          

Initplan (for Seq Scan)

5. 0.000 0.012 ↑ 1.0 1 1

Limit (cost=1.17..1.18 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=1)

6. 0.007 0.012 ↑ 1.0 1 1

Sort (cost=1.17..1.18 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=1)

  • Sort Key: qiscus_channels.created_at
  • Sort Method: quicksort Memory: 25kB
7. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on qiscus_channels (cost=0.00..1.16 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=1)

  • Filter: (app_id = 1)
  • Rows Removed by Filter: 13
8. 0.000 0.015 ↑ 1.0 1 1

Limit (cost=1.03..1.04 rows=1 width=12) (actual time=0.015..0.015 rows=1 loops=1)

9. 0.007 0.015 ↑ 1.0 1 1

Sort (cost=1.03..1.04 rows=1 width=12) (actual time=0.015..0.015 rows=1 loops=1)

  • Sort Key: fb_channels.created_at
  • Sort Method: quicksort Memory: 25kB
10. 0.008 0.008 ↓ 2.0 2 1

Seq Scan on fb_channels (cost=0.00..1.02 rows=1 width=12) (actual time=0.007..0.008 rows=2 loops=1)

  • Filter: (app_id = 1)
11. 0.000 0.011 ↑ 1.0 1 1

Limit (cost=1.06..1.06 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=1)

12. 0.007 0.011 ↑ 1.0 1 1

Sort (cost=1.06..1.06 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=1)

  • Sort Key: wa_channels.created_at
  • Sort Method: top-N heapsort Memory: 25kB
13. 0.004 0.004 ↓ 4.0 4 1

Seq Scan on wa_channels (cost=0.00..1.05 rows=1 width=12) (actual time=0.002..0.004 rows=4 loops=1)

  • Filter: (app_id = 1)
  • Rows Removed by Filter: 1
14. 0.001 0.012 ↑ 1.0 1 1

Limit (cost=1.02..1.03 rows=1 width=12) (actual time=0.011..0.012 rows=1 loops=1)

15. 0.007 0.011 ↑ 1.0 1 1

Sort (cost=1.02..1.03 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=1)

  • Sort Key: telegram_channels.created_at
  • Sort Method: quicksort Memory: 25kB
16. 0.004 0.004 ↓ 2.0 2 1

Seq Scan on telegram_channels (cost=0.00..1.01 rows=1 width=12) (actual time=0.003..0.004 rows=2 loops=1)

  • Filter: (app_id = 1)
17. 0.000 0.000 ↓ 0.0 0

Limit (cost=1.02..1.03 rows=1 width=12) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Sort (cost=1.02..1.03 rows=1 width=12) (never executed)

  • Sort Key: line_channels.created_at
19. 0.000 0.000 ↓ 0.0 0

Seq Scan on line_channels (cost=0.00..1.01 rows=1 width=12) (never executed)

  • Filter: (app_id = 1)
20.          

CTE filtered_services_id

21. 0.051 0.249 ↑ 1.8 119 1

HashAggregate (cost=24.52..26.63 rows=211 width=16) (actual time=0.234..0.249 rows=119 loops=1)

  • Group Key: s.room_log_id
22. 0.045 0.198 ↑ 1.3 166 1

Hash Semi Join (cost=4.55..23.46 rows=212 width=16) (actual time=0.053..0.198 rows=166 loops=1)

  • Hash Cond: (s.room_log_id = room_logs_raw.id)
23. 0.113 0.113 ↑ 1.1 320 1

Seq Scan on agent_services s (cost=0.00..15.63 rows=352 width=16) (actual time=0.003..0.113 rows=320 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 199
24. 0.021 0.040 ↓ 1.2 162 1

Hash (cost=2.80..2.80 rows=140 width=8) (actual time=0.040..0.040 rows=162 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
25. 0.019 0.019 ↓ 1.2 162 1

CTE Scan on room_logs_raw (cost=0.00..2.80 rows=140 width=8) (actual time=0.002..0.019 rows=162 loops=1)

26.          

CTE filtered_services

27. 0.063 0.520 ↑ 2.4 119 1

Hash Join (cost=9.25..29.49 rows=282 width=134) (actual time=0.352..0.520 rows=119 loops=1)

  • Hash Cond: (s_1.id = filtered_services_id.id)
28. 0.128 0.128 ↑ 1.1 519 1

Seq Scan on agent_services s_1 (cost=0.00..15.63 rows=563 width=134) (actual time=0.004..0.128 rows=519 loops=1)

29. 0.018 0.329 ↑ 1.7 119 1

Hash (cost=6.75..6.75 rows=200 width=8) (actual time=0.329..0.329 rows=119 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
30. 0.037 0.311 ↑ 1.7 119 1

HashAggregate (cost=4.75..6.75 rows=200 width=8) (actual time=0.299..0.311 rows=119 loops=1)

  • Group Key: filtered_services_id.id
31. 0.274 0.274 ↑ 1.8 119 1

CTE Scan on filtered_services_id (cost=0.00..4.22 rows=211 width=8) (actual time=0.235..0.274 rows=119 loops=1)

32.          

CTE customer_rooms

33. 0.081 1.087 ↑ 1.2 162 1

Hash Right Join (cost=16.05..24.71 rows=197 width=5,837) (actual time=0.771..1.087 rows=162 loops=1)

  • Hash Cond: (s_2.room_log_id = room_logs_raw_1.id)
34. 0.600 0.600 ↑ 2.4 119 1

CTE Scan on filtered_services s_2 (cost=0.00..5.64 rows=281 width=17) (actual time=0.354..0.600 rows=119 loops=1)

  • Filter: (user_id IS NOT NULL)
35. 0.081 0.406 ↓ 1.2 162 1

Hash (cost=14.30..14.30 rows=140 width=5,828) (actual time=0.406..0.406 rows=162 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
36. 0.059 0.325 ↓ 1.2 162 1

Hash Left Join (cost=10.68..14.30 rows=140 width=5,828) (actual time=0.036..0.325 rows=162 loops=1)

  • Hash Cond: (room_logs_raw_1.id = room_log_extras.room_log_id)
37. 0.252 0.252 ↓ 1.2 162 1

CTE Scan on room_logs_raw room_logs_raw_1 (cost=0.00..2.80 rows=140 width=4,748) (actual time=0.012..0.252 rows=162 loops=1)

38. 0.007 0.014 ↑ 3.3 9 1

Hash (cost=10.30..10.30 rows=30 width=1,088) (actual time=0.014..0.014 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
39. 0.007 0.007 ↑ 3.3 9 1

Seq Scan on room_log_extras (cost=0.00..10.30 rows=30 width=1,088) (actual time=0.006..0.007 rows=9 loops=1)

40. 0.017 2.757 ↓ 5.0 10 1

Sort (cost=1,907.12..1,907.13 rows=2 width=3,747) (actual time=2.757..2.757 rows=10 loops=1)

  • Sort Key: r.last_customer_timestamp
  • Sort Method: quicksort Memory: 29kB
41. 0.057 2.740 ↓ 9.0 18 1

Nested Loop Left Join (cost=1,903.30..1,907.11 rows=2 width=3,747) (actual time=2.657..2.740 rows=18 loops=1)

  • Join Filter: (users.id = r.agent_id)
  • Rows Removed by Join Filter: 699
42. 0.007 2.647 ↓ 9.0 18 1

Unique (cost=1,903.30..1,903.39 rows=2 width=3,719) (actual time=2.639..2.647 rows=18 loops=1)

43. 0.035 2.640 ↓ 9.0 18 1

Sort (cost=1,903.30..1,903.30 rows=2 width=3,719) (actual time=2.639..2.640 rows=18 loops=1)

  • Sort Key: r.id, r.name, r.room_badge, r.source, r.user_avatar_url, r.user_id, r.room_id, r.is_resolved, r.is_waiting, r.last_comment_timestamp, r.last_comment_text, r.last_customer_timestamp, r.last_comment_sender, r.last_comment_sender_type, r.channel_id, r.channel_id_2, r.is_handled_by_bot, r.agent_id
  • Sort Method: quicksort Memory: 29kB
44. 0.002 2.605 ↓ 9.0 18 1

Append (cost=0.00..1,903.29 rows=2 width=3,719) (actual time=1.024..2.605 rows=18 loops=1)

45. 1.245 1.245 ↓ 17.0 17 1

CTE Scan on customer_rooms r (cost=0.00..10.34 rows=1 width=3,719) (actual time=1.023..1.245 rows=17 loops=1)

  • Filter: (is_waiting AND (NOT is_resolved) AND ((agent_id IS NULL) OR (agent_id <> 6)) AND (((channel_id_2 = 13) AND ((source)::text = 'fb'::text)) OR ((channel_id_2 = 11) AND ((source)::text = 'line'::text)) OR ((channel_id_2 = 1) AND ((source)::text = 'paijem'::text)) OR ((channel_id_2 = 1) AND ((source)::text = 'qiscus'::text)) OR ((channel_id_2 = 38) AND ((source)::text = 'telegram'::text)) OR ((channel_id_2 = 23) AND ((source)::text = 'wa'::text))))
  • Rows Removed by Filter: 145
46. 0.123 1.358 ↑ 1.0 1 1

CTE Scan on customer_rooms r_1 (cost=0.00..1,892.92 rows=1 width=3,719) (actual time=1.026..1.358 rows=1 loops=1)

  • Filter: ((((channel_id_2 = 13) AND ((source)::text = 'fb'::text)) OR ((channel_id_2 = 11) AND ((source)::text = 'line'::text)) OR ((channel_id_2 = 1) AND ((source)::text = 'paijem'::text)) OR ((channel_id_2 = 1) AND ((source)::text = 'qiscus'::text)) OR ((channel_id_2 = 38) AND ((source)::text = 'telegram'::text)) OR ((channel_id_2 = 23) AND ((source)::text = 'wa'::text))) AND ((SubPlan 10) = 1))
  • Rows Removed by Filter: 161
47.          

SubPlan (for CTE Scan)

48. 0.095 1.235 ↑ 1.0 1 95

Aggregate (cost=9.55..9.56 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=95)

49. 0.079 1.140 ↓ 0.0 0 95

Hash Join (cost=7.06..9.54 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=95)

  • Hash Cond: (u.id = ags1.user_id)
50. 0.016 0.016 ↑ 2.1 9 2

Seq Scan on users u (cost=0.00..2.40 rows=19 width=8) (actual time=0.003..0.008 rows=9 loops=2)

  • Filter: ((deleted_at IS NULL) AND (force_offline OR (((NOT force_offline) OR (force_offline IS NULL)) AND (NOT is_available))))
  • Rows Removed by Filter: 12
51. 0.000 1.045 ↓ 0.0 0 95

Hash (cost=7.05..7.05 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=95)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
52. 1.045 1.045 ↓ 0.0 0 95

CTE Scan on filtered_services ags1 (cost=0.00..7.05 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=95)

  • Filter: ((NOT is_resolved) AND (user_id <> 6) AND (r_1.id = room_log_id))
  • Rows Removed by Filter: 119
53. 0.030 0.036 ↑ 1.0 39 18

Materialize (cost=0.00..2.60 rows=40 width=18) (actual time=0.000..0.002 rows=39 loops=18)

54. 0.006 0.006 ↑ 1.0 40 1

Seq Scan on users (cost=0.00..2.40 rows=40 width=18) (actual time=0.002..0.006 rows=40 loops=1)

Planning time : 1.171 ms
Execution time : 3.062 ms