performance - 为PostgreSQL查询选择正确的索引

简化表格：

CREATE TABLE products (
product_no integer PRIMARY KEY,
sales integer,
status varchar(16),
category varchar(16));

CREATE INDEX index_products_sales ON products (sales);
CREATE INDEX index_products_status ON products (status);
CREATE INDEX index_products_category ON products (category);

PostgreSQL版本是8.4。“状态”和“类别”列
有2000万种产品/行分布在15个类别中。
最常用的查询之一是获取三种最畅销的产品，不包括“cat3”和“cat7”类别的产品：

SELECT product_no, sales
FROM products
WHERE status = 'something' AND category NOT IN ('cat3', 'cat7')
ORDER BY sales DESC
LIMIT 3;

Limit  (cost=0.00..8833.39 rows=3 width=12) (actual time=9235.332..9356.284 rows=3 loops=1)
   ->  Index Scan using index_products_sales on products  (cost=0.00..68935806.85 rows=23412 width=12) (actual time=9235.327..9356.278 rows=3 loops=1)
     Filter: (((category)::text <> ALL ('{cat3,cat7}'::text[])) AND ((status)::text = 'something'::text))

使这个特定查询运行得更快的最佳索引是什么？

最佳答案

创建具有以下特定排序顺序的部分多列索引：

CREATE INDEX products_status_sales_partial_idx ON products (status, sales DESC)
WHERE  category NOT IN ('cat3','cat7');

稍微修改查询：

SELECT product_no, sales
FROM   products
WHERE  status = 'something'
AND    category NOT IN ('cat3', 'cat7')
ORDER  BY status, sales DESC
LIMIT  3;

将status添加为ORDER BY子句的第一个元素似乎是多余和毫无意义的。但是试试看。
为什么？
查询计划器不够聪明，无法理解

WHERE  status = 'something' ...
ORDER  BY sales DESC

索引(status, sales DESC)的排序顺序作为逻辑结果匹配。所以它将读取所有符合条件的行，排序并选择前3行。
通过将status添加到ORDER BY中，可以使查询计划器直接从索引中读取前3个条目。预计速度将提高几个数量级。
使用PostgreSQL 8.4和9.1进行测试。