如何索引这个join/group/order by？

Question

我有一个查询运行得很慢。我非常确定瓶颈是计划中的顺序扫描，所以我想构建适当的索引和/或重新安排查询以改进这一点。

下面是我的查询(和here is a fiddle with a schema and test data)：

SELECT conversations.id, max(messages.timestamp) as latest_message FROM
    conversations JOIN messages on conversations.id = messages.cid
    WHERE conversations.userid=1
    GROUP BY conversations.id ORDER BY latest_message;

我已经在所有相关列上建立了索引，并在cid和timestamp上进行了双向嵌套索引，但都无济于事。顺序扫描仍为：

 Sort  (cost=200.60..200.65 rows=20 width=12)
   Sort Key: (max(messages."timestamp"))
   ->  HashAggregate  (cost=199.97..200.17 rows=20 width=12)
         Group Key: conversations.id
         ->  Hash Join  (cost=11.50..197.97 rows=400 width=12)
               Hash Cond: (messages.cid = conversations.id)
               ->  Seq Scan on messages  (cost=0.00..160.00 rows=10000 width=12)
               ->  Hash  (cost=11.25..11.25 rows=20 width=4)
                     ->  Seq Scan on conversations  (cost=0.00..11.25 rows=20 width=4)
                           Filter: (userid = 10)

我如何改进这个查询和/或我可以构建哪些索引来修复这些顺序扫描？

Answer 1

对于这个版本的问题，我建议：

SELECT c.id,
       (SELECT max(m.timestamp) 
        FROM messages m
        WHERE c.id = m.cid
       ) as latest_message
FROM conversations c
WHERE c.userid = 1
ORDER BY latest_message;

您需要conversations(userid, cid)和messages(cid, timestamp)上的索引。