Carview!

explain analyze SELECT commentvotes.user_id AS commentvotes_user_id, count(commentvotes.user_id) AS count_1

FROM commentvotes JOIN comments ON comments.id = commentvotes.comment_id

WHERE comments.ghost = false AND comments.is_banned = false AND comments.deleted_utc = 0 AND commentvotes.vote_type = 1 AND comments.author_id = 1980 GROUP BY commentvotes.user_id ORDER BY count(commentvotes.user_id) DESC;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sort (cost=70749.90..70756.10 rows=2480 width=12) (actual time=3822.862..3881.672 rows=4348 loops=1)

Sort Key: (count(commentvotes.user_id)) DESC

Sort Method: quicksort Memory: 430kB

-> Finalize GroupAggregate (cost=70287.68..70610.08 rows=2480 width=12) (actual time=3815.283..3879.098 rows=4348 loops=1)

Group Key: commentvotes.user_id

-> Gather Merge (cost=70287.68..70572.88 rows=2480 width=12) (actual time=3815.274..3876.254 rows=7268 loops=1)

Workers Planned: 1

Workers Launched: 1

-> Sort (cost=69287.67..69293.87 rows=2480 width=12) (actual time=3808.996..3809.447 rows=3634 loops=2)

Sort Key: commentvotes.user_id

Sort Method: quicksort Memory: 293kB

Worker 0: Sort Method: quicksort Memory: 297kB

-> Partial HashAggregate (cost=69123.04..69147.84 rows=2480 width=12) (actual time=3807.327..3807.997 rows=3634 loops=2)

Group Key: commentvotes.user_id

Batches: 1 Memory Usage: 625kB

Worker 0: Batches: 1 Memory Usage: 721kB

-> Nested Loop (cost=1.00..68446.39 rows=135331 width=4) (actual time=0.700..3718.044 rows=307736 loops=2)

-> Parallel Index Scan using comments_author_id_id_idx on comments (cost=0.43..28058.16 rows=16088 width=4) (actual time=0.238..139.008 rows=14304 loops=2)

Index Cond: (author_id = 1980)

Filter: ((NOT ghost) AND (NOT is_banned) AND (deleted_utc = 0))

Rows Removed by Filter: 291

-> Index Only Scan using commentvotes_commentid_userid_votetype_idx on commentvotes (cost=0.56..2.18 rows=33 width=8) (actual time=0.225..0.247 rows=22 loops=28609)

Index Cond: ((comment_id = comments.id) AND (vote_type = 1))

Heap Fetches: 4303

Planning Time: 3.849 ms

Execution Time: 3882.337 ms

(26 rows)

Untitled