explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sL4i

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 1.176 ↑ 1.0 10 1

WindowAgg (cost=89,426.86..89,427.21 rows=10 width=116) (actual time=1.162..1.176 rows=10 loops=1)

2.          

CTE in_interval

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1)

4. 0.002 1.163 ↑ 1.0 10 1

Subquery Scan on v (cost=89,426.85..89,426.98 rows=10 width=104) (actual time=1.158..1.163 rows=10 loops=1)

5. 0.003 1.161 ↑ 1.0 10 1

Limit (cost=89,426.85..89,426.88 rows=10 width=100) (actual time=1.157..1.161 rows=10 loops=1)

6. 0.017 1.158 ↑ 20.0 10 1

Sort (cost=89,426.85..89,427.35 rows=200 width=100) (actual time=1.157..1.158 rows=10 loops=1)

  • Sort Key: ((('5'::numeric * sum(((0)::bigint))) + ('2'::numeric * sum("*SELECT* 1".film_rating_count)))) DESC
  • Sort Method: top-N heapsort Memory: 25kB
7. 0.030 1.141 ↑ 9.5 21 1

GroupAggregate (cost=89,361.80..89,422.53 rows=200 width=100) (actual time=1.114..1.141 rows=21 loops=1)

  • Group Key: "*SELECT* 1".user_id
8. 0.009 1.111 ↑ 160.1 23 1

Sort (cost=89,361.80..89,371.01 rows=3,682 width=20) (actual time=1.108..1.111 rows=23 loops=1)

  • Sort Key: "*SELECT* 1".user_id
  • Sort Method: quicksort Memory: 26kB
9. 0.002 1.102 ↑ 160.1 23 1

Append (cost=82,402.44..89,106.89 rows=3,682 width=20) (actual time=0.648..1.102 rows=23 loops=1)

10. 0.005 0.680 ↑ 169.2 21 1

Subquery Scan on *SELECT* 1 (cost=82,402.44..82,509.06 rows=3,554 width=20) (actual time=0.648..0.680 rows=21 loops=1)

11. 0.016 0.675 ↑ 169.2 21 1

GroupAggregate (cost=82,402.44..82,464.64 rows=3,554 width=16) (actual time=0.647..0.675 rows=21 loops=1)

  • Group Key: fr.user_id
12.          

Initplan (for GroupAggregate)

13. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on in_interval in_interval_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1)

14. 0.033 0.658 ↑ 28.9 123 1

Sort (cost=82,402.42..82,411.31 rows=3,554 width=4) (actual time=0.644..0.658 rows=123 loops=1)

  • Sort Key: fr.user_id
  • Sort Method: quicksort Memory: 30kB
15. 0.091 0.625 ↑ 28.9 123 1

Nested Loop (cost=0.99..82,192.82 rows=3,554 width=4) (actual time=0.030..0.625 rows=123 loops=1)

16. 0.082 0.082 ↓ 1.3 113 1

Index Scan using users_country_id_idx on users u (cost=0.42..196.49 rows=84 width=4) (actual time=0.006..0.082 rows=113 loops=1)

  • Index Cond: (country_id = 16)
17. 0.452 0.452 ↑ 246.0 1 113

Index Only Scan using film_ratings_user_id_inserted_datetime_idx on film_ratings fr (cost=0.57..973.69 rows=246 width=4) (actual time=0.003..0.004 rows=1 loops=113)

  • Index Cond: ((user_id = u.id) AND (inserted_datetime >= (('now'::cstring)::date - $2)))
  • Heap Fetches: 123
18. 0.001 0.420 ↑ 64.0 2 1

Subquery Scan on *SELECT* 2 (cost=6,593.99..6,597.83 rows=128 width=20) (actual time=0.419..0.420 rows=2 loops=1)

19. 0.001 0.419 ↑ 64.0 2 1

GroupAggregate (cost=6,593.99..6,596.23 rows=128 width=20) (actual time=0.418..0.419 rows=2 loops=1)

  • Group Key: fc.user_id
20.          

Initplan (for GroupAggregate)

21. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on in_interval (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=1)

22. 0.005 0.418 ↑ 42.7 3 1

Sort (cost=6,593.97..6,594.29 rows=128 width=4) (actual time=0.417..0.418 rows=3 loops=1)

  • Sort Key: fc.user_id
  • Sort Method: quicksort Memory: 25kB
23. 0.115 0.413 ↑ 42.7 3 1

Nested Loop (cost=0.86..6,589.49 rows=128 width=4) (actual time=0.310..0.413 rows=3 loops=1)

24. 0.072 0.072 ↓ 1.3 113 1

Index Scan using users_country_id_idx on users u_1 (cost=0.42..196.49 rows=84 width=4) (actual time=0.005..0.072 rows=113 loops=1)

  • Index Cond: (country_id = 16)
25. 0.226 0.226 ↓ 0.0 0 113

Index Only Scan using film_comments_user_id_inserted_datetime_idx on film_comments fc (cost=0.43..75.93 rows=18 width=4) (actual time=0.002..0.002 rows=0 loops=113)

  • Index Cond: ((user_id = u_1.id) AND (inserted_datetime >= (('now'::cstring)::date - $1)))
  • Heap Fetches: 3
Planning time : 0.560 ms
Execution time : 1.271 ms