explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cJr8

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

Limit (cost=576.79..576.81 rows=10 width=3,711) (actual time=13.107..13.111 rows=10 loops=1)

2.          

CTE room_logs_raw

3. 0.198 0.292 ↓ 1.2 162 1

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

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

6. 0.011 0.020 ↑ 1.0 1 1

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

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

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

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

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

9. 0.017 0.024 ↑ 1.0 1 1

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

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

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

  • Filter: (app_id = 1)
11. 0.001 0.024 ↑ 1.0 1 1

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

12. 0.015 0.023 ↑ 1.0 1 1

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

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

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

  • Filter: (app_id = 1)
  • Rows Removed by Filter: 1
14. 0.002 0.024 ↑ 1.0 1 1

Limit (cost=1.02..1.03 rows=1 width=12) (actual time=0.023..0.024 rows=1 loops=1)

15. 0.013 0.022 ↑ 1.0 1 1

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

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

Seq Scan on telegram_channels (cost=0.00..1.01 rows=1 width=12) (actual time=0.007..0.009 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

21. 0.279 1.290 ↑ 1.3 165 1

HashAggregate (cost=24.52..26.64 rows=212 width=142) (actual time=1.194..1.290 rows=165 loops=1)

  • Group Key: s.id
22. 0.120 1.011 ↑ 1.3 165 1

Hash Semi Join (cost=4.55..23.46 rows=212 width=134) (actual time=0.636..1.011 rows=165 loops=1)

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

Seq Scan on agent_services s (cost=0.00..15.63 rows=352 width=134) (actual time=0.010..0.286 rows=319 loops=1)

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

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

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

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

26.          

CTE customer_rooms

27. 0.153 12.539 ↓ 1.1 162 1

Hash Left Join (cost=15.23..529.83 rows=148 width=5,829) (actual time=7.934..12.539 rows=162 loops=1)

  • Hash Cond: (room_logs_raw_1.id = room_log_extras.room_log_id)
28. 0.000 12.345 ↓ 1.1 162 1

Hash Right Join (cost=4.55..518.27 rows=148 width=4,749) (actual time=7.872..12.345 rows=162 loops=1)

  • Hash Cond: (s_1.id = (SubPlan 8))
29. 0.041 0.041 ↑ 1.3 165 1

CTE Scan on filtered_services s_1 (cost=0.00..4.24 rows=212 width=9) (actual time=0.000..0.041 rows=165 loops=1)

30. 7.732 7.807 ↓ 1.2 162 1

Hash (cost=2.80..2.80 rows=140 width=4,748) (actual time=7.807..7.807 rows=162 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
31. 0.075 0.075 ↓ 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.025..0.075 rows=162 loops=1)

32.          

SubPlan (for Hash Right Join)

33. 0.280 11.760 ↑ 1.0 1 280

Limit (cost=4.78..4.78 rows=1 width=16) (actual time=0.041..0.042 rows=1 loops=280)

34. 0.840 11.480 ↑ 1.0 1 280

Sort (cost=4.78..4.78 rows=1 width=16) (actual time=0.041..0.041 rows=1 loops=280)

  • Sort Key: filtered_services.created_at DESC
  • Sort Method: quicksort Memory: 25kB
35. 10.640 10.640 ↑ 1.0 1 280

CTE Scan on filtered_services (cost=0.00..4.77 rows=1 width=16) (actual time=0.022..0.038 rows=1 loops=280)

  • Filter: (room_log_id = room_logs_raw_1.id)
  • Rows Removed by Filter: 164
36. 0.020 0.041 ↑ 3.3 9 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
37. 0.021 0.021 ↑ 3.3 9 1

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

38. 0.173 13.108 ↑ 14.8 10 1

Sort (cost=6.16..6.53 rows=148 width=3,711) (actual time=13.106..13.108 rows=10 loops=1)

  • Sort Key: r.last_comment_timestamp DESC
  • Sort Method: top-N heapsort Memory: 30kB
39. 12.935 12.935 ↓ 1.1 162 1

CTE Scan on customer_rooms r (cost=0.00..2.96 rows=148 width=3,711) (actual time=7.941..12.935 rows=162 loops=1)

Planning time : 1.323 ms
Execution time : 13.536 ms