postgresql - 使用函数索引优化 PostgreSQL？

Question

在进行一些性能调整时，我看到了 Instagram 工程团队的帖子:

http://instagram-engineering.tumblr.com/post/40781627982/handling-growth-with-postgres-5-tips-from-instagram

On some of our tables, we need to index strings (for example, 64 character base64 tokens) that are quite long, and creating an index on those strings ends up duplicating a lot of data. For these, Postgres’ functional index feature can be very helpful:

CREATE INDEX CONCURRENTLY on tokens (substr(token), 0, 8)

While there will be multiple rows that match that prefix, having Postgres match those prefixes and then filter down is quick, and the resulting index was 1/10th the size it would have been had we indexed the entire string.

这看起来是个好主意，所以我试了一下——我们有很多项目都是用校验和作为键控的。

我们的结果并不好。我想知道是否还有其他人运气好。

首先，博文看起来不对:

CREATE INDEX CONCURRENTLY on tokens (substr(token), 0, 8)

那不应该是...

CREATE INDEX CONCURRENTLY on tokens (substr(token, 0, 8));

我们的一个字段基于 40 个字符的散列。所以我尝试了:

CREATE INDEX __speed_idx_test_8 on foo (substr(bar, 0, 8));

查询规划器不会使用它。

所以我尝试了:

CREATE INDEX __speed_idx_test_20 on foo (substr(bar, 0, 20));

查询规划器仍然不会使用它。

然后我试了:

CREATE INDEX __speed_idx_test_40 on foo (substr(bar, 0, 40));

不过，计划者不会使用它。

如果我们尝试禁用序列扫描会怎样？

set enable_seqscan=false;

没有。

让我们回到原来的索引。

CREATE INDEX __speed_idx_original on foo (bar);
set enable_seqscan = True;

那行得通。

然后我想——也许我需要在查询中使用函数才能使用函数索引。所以我尝试更改查询:

旧的:

select * from foo where hash = '%s';

新的

select * from foo where substr(hash,0,8) = '%s' and hash = '%s';

这奏效了。

有谁知道是否可以在不添加额外搜索条件的情况下完成这项工作？我宁愿不这样做，但看着文件大小和速度的改进......哇。

如果您想知道“解释分析”输出是什么...

-- seq scan
Seq Scan on foo  (cost=10000000000.00..10000073130.77 rows=1 width=1921) (actual time=373.785..1563.551 rows=1 loops=1)
  Filter: (hash = 'eae1d1728963f107fa7d8136bcf7c72572896e1d'::bpchar)
  Rows Removed by Filter: 450252
Total runtime: 1563.687 ms


-- index scan
Index Scan using __speed_idx_original on foo  (cost=0.00..16.53 rows=1 width=1920) (actual time=0.060..0.061 rows=1 loops=1)
  Index Cond: (hash = 'eae1d1728963f107fa7d8136bcf7c72572896e1d'::bpchar)
Total runtime: 1.501 m


-- index scan with substring function
 Index Scan using __speed_idx_test_8 on foo  (cost=0.00..16.37 rows=1 width=1913) (actual time=0.134..0.134 rows=0 loops=1)
  Index Cond: (substr((hash)::text, 0, 8) = 'eae1d172'::text)
  Filter: (hash = 'eae1d1728963f107fa7d8136bcf7c72572896e1d'::bpchar)
Total runtime: 0.216 ms

Answer 1

只有在 WHERE 子句中使用函数时它才有效。函数签名充当查询计划器的提示，即从函数返回的标量值包含在索引中。这仅适用于不可变函数。无法使用此方法对可变函数(每次调用都不会返回相同结果的函数，如 rand())进行索引。