CODEWITHSUNDEEP
sqlpostgresql
Nick Barrett
Nick Barrett

Reputation: 1051

Slow query performance using GROUP BY, ORDER BY and LIMIT

I am having a problem with this query running on a table with 550k rows

SELECT "items".* FROM "items" 
WHERE (items.player_id = '1') 
GROUP BY items.id 
ORDER BY items.created_at DESC 
LIMIT 50 OFFSET 0

The explain analyse indicates a serious problem with the ORDER BY part, which is taking 5.7 secs.

"Limit  (cost=64509.79..64509.91 rows=50 width=550) (actual time=5767.488..5767.499 rows=50 loops=1)"
"  ->  Sort  (cost=64509.79..65867.39 rows=543041 width=550) (actual time=5767.486..5767.492 rows=50 loops=1)"
"        Sort Key: created_at"
"        Sort Method: top-N heapsort  Memory: 50kB"
"        ->  Group  (cost=0.42..46470.36 rows=543041 width=550) (actual time=0.105..2668.933 rows=543024 loops=1)"
"              ->  Index Scan using items_pkey on items  (cost=0.42..45112.76 rows=543041 width=550) (actual time=0.099..989.441 rows=543024 loops=1)"
"                    Filter: (player_id = 1)"
"                    Rows Removed by Filter: 252"
"Total runtime: 5767.814 ms"

I have indexes on items.id, items.created_at, items.player_id, (items.id, items.created_at), (items.player_id, items.created_at), (items.id, items.player_id, items.created_at)

Upvotes: 0

Views: 1318

Answers (2)

Prafful Garg
Prafful Garg

Reputation: 214

Basically you don't need to the GROUP BY clause, as this works only with aggregate functions, so remove that GROUP BY and the query performance will definitely get better.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Why do you need the group by? The aggregation only works when items.id is unique -- which in turn means that the aggregation is not needed.

SELECT i.*
FROM items i
WHERE (i.player_id = '1') 
ORDER BY items.created_at DESC 
LIMIT 50 OFFSET 0;

You can then improve this query by adding an index on items(player_id, created_at).

Upvotes: 2

Related Questions