explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LbdU

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 2.231 ↑ 1.0 10 1

Limit (cost=103.20..103.23 rows=10 width=3,711) (actual time=2.228..2.231 rows=10 loops=1)

2.          

CTE room_logs_raw

3. 0.120 0.183 ↓ 1.2 162 1

Seq Scan on room_logs r_1 (cost=5.34..14.16 rows=140 width=838) (actual time=0.011..0.183 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.017 ↑ 1.0 1 1

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

6. 0.009 0.016 ↑ 1.0 1 1

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

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

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

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

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

9. 0.010 0.014 ↑ 1.0 1 1

Sort (cost=1.03..1.04 rows=1 width=12) (actual time=0.014..0.014 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.001 0.015 ↑ 1.0 1 1

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

12. 0.009 0.014 ↑ 1.0 1 1

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

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

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

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

Limit (cost=1.02..1.03 rows=1 width=12) (actual time=0.016..0.017 rows=1 loops=1)

15. 0.010 0.016 ↑ 1.0 1 1

Sort (cost=1.02..1.03 rows=1 width=12) (actual time=0.016..0.016 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.120 0.491 ↑ 1.8 118 1

HashAggregate (cost=24.52..26.63 rows=211 width=16) (actual time=0.460..0.491 rows=118 loops=1)

  • Group Key: s.room_log_id
22. 0.078 0.371 ↑ 1.3 165 1

Hash Semi Join (cost=4.55..23.46 rows=212 width=16) (actual time=0.081..0.371 rows=165 loops=1)

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

Seq Scan on agent_services s (cost=0.00..15.63 rows=352 width=16) (actual time=0.005..0.230 rows=319 loops=1)

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 197
24. 0.034 0.063 ↓ 1.2 162 1

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

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

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

26.          

CTE filtered_services

27. 0.102 0.985 ↑ 2.4 118 1

Hash Join (cost=9.25..29.49 rows=282 width=134) (actual time=0.698..0.985 rows=118 loops=1)

  • Hash Cond: (s_1.id = filtered_services_id.id)
28. 0.223 0.223 ↑ 1.1 516 1

Seq Scan on agent_services s_1 (cost=0.00..15.63 rows=563 width=134) (actual time=0.006..0.223 rows=516 loops=1)

29. 0.031 0.660 ↑ 1.7 118 1

Hash (cost=6.75..6.75 rows=200 width=8) (actual time=0.660..0.660 rows=118 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
30. 0.088 0.629 ↑ 1.7 118 1

HashAggregate (cost=4.75..6.75 rows=200 width=8) (actual time=0.607..0.629 rows=118 loops=1)

  • Group Key: filtered_services_id.id
31. 0.541 0.541 ↑ 1.8 118 1

CTE Scan on filtered_services_id (cost=0.00..4.22 rows=211 width=8) (actual time=0.462..0.541 rows=118 loops=1)

32.          

CTE customer_rooms

33. 0.158 1.853 ↑ 1.2 162 1

Hash Right Join (cost=16.05..24.72 rows=197 width=5,829) (actual time=1.332..1.853 rows=162 loops=1)

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

CTE Scan on filtered_services s_2 (cost=0.00..5.64 rows=282 width=9) (actual time=0.701..1.096 rows=118 loops=1)

35. 0.124 0.599 ↓ 1.2 162 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 42kB
36. 0.095 0.475 ↓ 1.2 162 1

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

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

38. 0.007 0.015 ↑ 3.3 9 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
39. 0.008 0.008 ↑ 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.008 rows=9 loops=1)

40. 0.117 2.228 ↑ 19.7 10 1

Sort (cost=8.20..8.69 rows=197 width=3,711) (actual time=2.227..2.228 rows=10 loops=1)

  • Sort Key: r.last_comment_timestamp DESC
  • Sort Method: top-N heapsort Memory: 28kB
41. 2.111 2.111 ↑ 1.2 162 1

CTE Scan on customer_rooms r (cost=0.00..3.94 rows=197 width=3,711) (actual time=1.336..2.111 rows=162 loops=1)

Planning time : 0.729 ms
Execution time : 2.483 ms