我在一个有550k行的表上运行此查询时遇到问题
SELECT "items".* FROM "items"
WHERE (items.player_id = '1')
GROUP BY items.id
ORDER BY items.created_at DESC
LIMIT 50 OFFSET 0
解释分析表明部分订单出现严重问题，需要5.7秒。
"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"
我有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）的索引
最佳答案
基本上不需要使用GROUPBY子句，因为这只适用于聚合函数，所以删除GROUPBY，查询性能肯定会更好。
关于sql - 使用GROUP BY，ORDER BY和LIMIT降低查询性能，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22041075/