PostgreSQL9.6中的查询计划非常糟糕

如果您在PostgreSQL 9.6中遇到了查询计划糟糕的问题，则可能需要重新构造查询或优化索引以获取更好的查询性能。以下示例显示如何通过使用索引来优化查询来解决此类问题。

假设您有一个包含以下列的表：

CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_id INT NOT NULL, order_date DATE NOT NULL, total_amount NUMERIC(10,2) NOT NULL );

如果您想查找在特定日期范围内下了多少订单以及每个客户的订单总额，则可以使用以下查询：

SELECT order_date, customer_id, SUM(total_amount) FROM orders WHERE order_date BETWEEN '2021-01-01' AND '2021-01-31' GROUP BY order_date, customer_id;

尽管这是一个非常基本的查询，但是在大型表上执行时，它可能会导致非常糟糕的查询性能。

为了优化此查询，您可以添加一个复合索引：

CREATE INDEX orders_date_cusidx ON orders (order_date, customer_id);

这将使查询使用索引来满足WHERE子句，并在GROUP BY子句上进行分组。

这是一个执行计划的示例：

HashAggregate (cost=60507.64..60508.31 rows=67 width=20) Group Key: order_date, customer_id -> Bitmap Heap Scan on orders (cost=1386.23..57852.76 rows=106625 width=20) Recheck Cond: ((order_date >= '2021-01-01'::date) AND (order_date <= '2021-01-31'::date)) -> Bitmap Index Scan on orders_date_cusidx (cost=0.00..1361.69 rows=106625 width=0) Index Cond: ((order_date >= '2021-01-01'::date) AND (order_date <= '2021-01-31'::date))

这个执行计划比没有索引的查询计划优化了很多，因为它使用一个索引扫描来满足WHERE子句，并使用哈希聚合来计算每个客户的总金额。

在平时的开发过程中，您还可以使用EXPLAIN