explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cELu

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

Limit (cost=2,008.20..2,008.21 rows=2 width=3,747) (actual time=3.147..3.150 rows=10 loops=1)

2.          

CTE room_logs_raw

3. 0.091 0.144 ↓ 1.2 162 1

Seq Scan on room_logs r_2 (cost=5.34..14.16 rows=140 width=838) (actual time=0.014..0.144 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.001 0.015 ↑ 1.0 1 1

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

6. 0.008 0.014 ↑ 1.0 1 1

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

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

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

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

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

9. 0.006 0.010 ↑ 1.0 1 1

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

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

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

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

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

12. 0.009 0.013 ↑ 1.0 1 1

Sort (cost=1.06..1.06 rows=1 width=12) (actual time=0.013..0.013 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.003..0.004 rows=4 loops=1)

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

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

15. 0.008 0.014 ↑ 1.0 1 1

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

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

Seq Scan on telegram_channels (cost=0.00..1.01 rows=1 width=12) (actual time=0.005..0.006 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.022 0.316 ↑ 1.8 119 1

Unique (cost=31.65..32.71 rows=211 width=16) (actual time=0.286..0.316 rows=119 loops=1)

22. 0.061 0.294 ↑ 1.3 166 1

Sort (cost=31.65..32.18 rows=212 width=16) (actual time=0.286..0.294 rows=166 loops=1)

  • Sort Key: s.room_log_id, s.id DESC
  • Sort Method: quicksort Memory: 32kB
23. 0.056 0.233 ↑ 1.3 166 1

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

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

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

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 199
25. 0.026 0.049 ↓ 1.2 162 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
26. 0.023 0.023 ↓ 1.2 162 1

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

27.          

CTE filtered_services

28. 0.075 0.665 ↑ 2.4 119 1

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

  • Hash Cond: (s_1.id = filtered_services_id.id)
29. 0.168 0.168 ↑ 1.1 519 1

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

30. 0.022 0.422 ↑ 1.7 119 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
31. 0.061 0.400 ↑ 1.7 119 1

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

  • Group Key: filtered_services_id.id
32. 0.339 0.339 ↑ 1.8 119 1

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

33.          

CTE customer_rooms

34. 0.095 1.352 ↑ 1.2 162 1

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

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

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

  • Filter: (user_id IS NOT NULL)
36. 0.094 0.493 ↓ 1.2 162 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
37. 0.074 0.399 ↓ 1.2 162 1

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

  • Hash Cond: (room_logs_raw_1.id = room_log_extras.room_log_id)
38. 0.308 0.308 ↓ 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.017..0.308 rows=162 loops=1)

39. 0.010 0.017 ↑ 3.3 9 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
40. 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.005..0.007 rows=9 loops=1)

41. 0.024 3.147 ↓ 5.0 10 1

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

  • Sort Key: r.last_customer_timestamp
  • Sort Method: quicksort Memory: 29kB
42. 0.041 3.123 ↓ 9.0 18 1

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

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

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

44. 0.039 3.021 ↓ 9.0 18 1

Sort (cost=1,903.30..1,903.30 rows=2 width=3,719) (actual time=3.020..3.021 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
45. 0.002 2.982 ↓ 9.0 18 1

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

46. 1.538 1.538 ↓ 17.0 17 1

CTE Scan on customer_rooms r (cost=0.00..10.34 rows=1 width=3,719) (actual time=1.293..1.538 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
47. 0.112 1.442 ↑ 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.087..1.442 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
48.          

SubPlan (for CTE Scan)

49. 0.095 1.330 ↑ 1.0 1 95

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

50. 0.077 1.235 ↓ 0.0 0 95

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

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

Seq Scan on users u (cost=0.00..2.40 rows=19 width=8) (actual time=0.004..0.009 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
52. 0.000 1.140 ↓ 0.0 0 95

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
53. 1.140 1.140 ↓ 0.0 0 95

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

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

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

55. 0.009 0.009 ↑ 1.0 40 1

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

Planning time : 1.584 ms
Execution time : 3.774 ms