postgresql - 提高多列索引和排序的性能

Question

SELECT * FROM table1
WHERE (col1, col2) IN (($1, $2), ($3, $4))
ORDER  BY col3
LIMIT  10;

EXPLAIN ANALYZE 的输出:

 Limit  (cost=59174.75..59174.77 rows=10 width=113) (actual time=3632.627..3632.661 rows=10 loops=1)
   ->  Sort  (cost=59174.75..59180.22 rows=2188 width=113) (actual time=3632.623..3632.634 rows=10 loops=1)
         Sort Key: col3
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Nested Loop  (cost=2.62..59127.46 rows=2188 width=113) (actual time=0.234..3561.309 rows=38347 loops=1)
   ...........
   Total runtime: 3632.818 ms

但是当我删除订单时:

SELECT * FROM table1 WHERE (col1, col2) IN (($1, $2), ($3, $4)) LIMIT 10;
 Limit  (cost=2.62..272.85 rows=10 width=105) (actual time=0.258..1.143 rows=10 loops=1)
   ->  Nested Loop  (cost=2.62..59127.46 rows=2188 width=105) (actual time=0.255..1.115 rows=10 loops=1)
........
Total runtime: 1.306 ms

1. 在 (col1, col2) 上有一个复合 btree 索引，在 col3 上有一个 btree 索引。
2. 写入性能和存储不是优先事项。读取性能至关重要，需要尽可能快。
3. 这必须能够支持使用 IN 子句进行查询:WHERE (col1, col2) IN (($1, $2), ($3, $4)) ORDER BY col3 LIMIT 10;。 (查找始终使用 IN 子句，然后进行排序。)

注意:是否可以在 (col1, col2, col3) 上创建索引？这将使用 (col1, col2) 进行查找并且已经订购了 col3 ...

Answer 1

是的。您已经在问题中找到了答案。
对于给定的查询，一个 multicolumn index (col1, col2, col3) 应该是完美的。试试吧。

在 dba.SE 上的这个相关问题下，更多关于多列 B 树索引中列顺序的信息:
Is a composite index also good for queries on the first field?

此外，如果您实际上不需要 table1 中的所有列，只需将需要的列放在 SELECT 列表中，而不是 * 以获得性能。

在

至于您的附加要求:

WHERE (col1, col2) IN (($1, $2), ($3, $4))

相当于:

WHERE (col1 = $1 AND col2 = $2 OR
       col1 = $3 AND col2 = $4)

这降低了索引相对于 (col1, col2, col3) 的有效性，因为 Postgres 不能只从索引中获取预先排序的列表。这取决于。 IN 列表中的项目越少，每个 (col1, col2) 具有相同 col3 的行越多，您获得的 yield 就越多来自所述索引。

您必须进行测试。另外创建索引，确保你的 server is configured reasonably , 统计数据是最新的 ( ANALYZE ) 和你的 cost settings是合理的，然后 EXPLAIN 将显示 Postgres 选择的内容。请务必运行一组代表您的用例的查询。最后，删除不使用的索引。

诱使 Postgres 有效地使用特殊索引

排序步骤似乎是最昂贵的部分。试试这个替代查询:IN 列表中的每个项目一个 UNION ALL 分支。这向 Postgres 提出了一个它无法拒绝的提议:特殊索引非常适合这个查询的分支。最后的排序步骤对于只有少数 IN 项来说是便宜的。

(
SELECT *
FROM   table1
WHERE  col1 = $1 AND col2 = $3
ORDER  BY col3
LIMIT  10
)
UNION  ALL
(
SELECT *
FROM   table1
WHERE  col1 = $3 AND col2 = $4
ORDER  BY col3
LIMIT  10
)
... UNION  ALL ...
ORDER  BY col3
LIMIT  10

请注意，除了最后的 ORDER BY 和 之外，所有括号都需要允许 ORDER BY 和 LIMIT 用于每条腿限制。