为PostgreSQL查询选择正确的索引[英] Chosing the right index for PostgreSQL query

本文是小编为大家收集整理的关于为PostgreSQL查询选择正确的索引的处理方法,想解了为PostgreSQL查询选择正确的索引的问题怎么解决?为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 测试.

本文地址:https://www.itbaoku.cn/post/1764041.html