提高多色索引和排序的性能
提高多色索引和排序的性能
提问于 2014-03-17 19:33:22
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- 有一个复合
btree index on (col1, col2)和一个btree index on col3。 - 写性能和存储不是优先考虑的问题。读取性能是最重要的，需要尽可能快。
- 这必须能够支持IN子句的查询：
WHERE (col1, col2) IN (($1, $2), ($3, $4)) ORDER BY col3 LIMIT 10;。(“查找”总是带有IN子句，然后是订单。)
注意:是否可以在(col1、col2、col3)上创建索引？用(col1, col2)查一下col3已经订好了.
回答 1
Stack Overflow用户
回答已采纳
发布于 2014-03-17 19:37:15
是。你已经在这个问题上得到了答案。
对于给定的查询，多色指数 on (col1, col2, col3)应该是完美的。就试一下。
更多关于多色B-树索引中列顺序的信息，在dba.SE上的相关问题下：
此外，如果实际上不需要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值越多，从所述索引中获得的收益就越多。
你得测试一下。另外创建索引，确保您的服务器配置合理、统计信息是最新的(ANALYZE)，并且您的成本设置是合理的，然后EXPLAIN将显示Postgres选择的内容。一定要运行一组表示用例的查询。最后，删除不被使用的索引。
使Postgres有效地使用特殊索引
这一步似乎是昂贵的部分。尝试以下替代查询:在您的UNION ALL列表中，每个项目有一个IN腿。这给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和LIMIT之外，还需要所有括号才能允许对每一段进行ORDER BY和LIMIT。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/22463663
复制相关文章
相似问题
腾讯云开发者
