如何优化此全文搜索查询？(tsvector) (1.5mil行)

Question

我目前在PostgreSQL中有一个全文搜索查询(见下文)，它扫描包含150万行的单个表，查找与“所有”术语和“任何”术语匹配的所有条目。

对于结果很少的查询，查询可以正确执行，并且执行速度一般(~2-3秒)。在100,000+匹配的结果上以可怕的速度(大约15-100秒)

查询首先按术语类型对结果进行排序(先是所有匹配的术语，然后是匹配的任何术语)，然后通过ts_rank_cd相关性计算对结果进行子排序。(以及更简单的变体，它按可索引的已知列进行排序，如持续时间)

SELECT
  *,
  ts_rank_cd(textsearchable, query_any_terms) AS relevance,
  textsearchable @@ query_all_terms AS all_terms,
  sum(1) over (PARTITION BY textsearchable @@ query_all_terms) AS match_method_total,
  sum(1) over () AS all_matched_total
FROM
  videos,
  to_tsquery(?) AS query_any_terms,
  to_tsquery(?) AS query_all_terms
WHERE
  website IN (?)
  AND textsearchable @@ query_any_terms
  AND duration_in_seconds >= ?
  AND duration_in_seconds <= ?
ORDER BY
  all_terms DESC, 
  relevance DESC 
LIMIT ? 
OFFSET ?

所有相关列都已编入索引，系统监控显示内存和cpu未得到最充分利用，瓶颈似乎是磁盘IO。

服务器是Ubuntu server 10.04。内存和cpu能力可以根据需要通过后台轻松增加，以满足解决方案。

目前，数据库在生产中是静态的，不会在生产服务器上写入数据库。因此，如果这是有益的，则完全和准确地生成保持准确的索引是可能的。

任何对寻找改进途径的帮助都将不胜感激。如有需要，可及时提供附加信息。

Answer 1

使用tmpfs将DB加载到ram中，查询时间显著改善(即从大约100秒到大约20秒)。

请参阅：http://www.slideshare.net/pgconf/five-steps-perform2009