Postgress在查询索引表达式视图时使用了错误的索引

Question

当我用Postgres 9.3运行以下脚本(enable_seqscan设置为off)时，我希望最终查询使用"forms_string"部分索引，但使用"forms_int"索引，这没有意义。

当我用JSON函数和更多类型的索引的实际代码测试这一点时，它似乎一直在为每个查询使用上一个创建的索引。

添加更多不相关的行，使与部分索引相关的行仅占表中总行数的一小部分，会导致"位图堆扫描"，但之后仍然会提到相同的错误索引。

你知道我怎样才能让它使用正确的索引吗？

CREATE EXTENSION IF NOT EXISTS plv8;

CREATE OR REPLACE FUNCTION
json_string(data json, key text) RETURNS TEXT AS $$
var ret = data,
keys = key.split('.'),
len = keys.length;
for (var i = 0; i < len; ++i) {
if (ret) {
ret = ret[keys[i]]
};
}
if (typeof ret === "undefined") {
ret = null;
} else if (ret) {
ret = ret.toString();
}
return ret;
$$ LANGUAGE plv8 IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION
json_int(data json, key text) RETURNS INT AS $$
var ret = data,
keys = key.split('.'),
len = keys.length;
for (var i = 0; i < len; ++i) {
if (ret) {
ret = ret[keys[i]]
}
}
if (typeof ret === "undefined") {
ret = null;
} else {
ret = parseInt(ret, 10);
if (isNaN(ret)) {
ret = null;
}
}
return ret;
$$ LANGUAGE plv8 IMMUTABLE STRICT;
CREATE TABLE form_types (
id SERIAL NOT NULL, 
name VARCHAR(200), 
PRIMARY KEY (id)
);
CREATE TABLE tenants (
id SERIAL NOT NULL, 
name VARCHAR(200), 
PRIMARY KEY (id)
);
CREATE TABLE forms (
id SERIAL NOT NULL, 
tenant_id INTEGER, 
type_id INTEGER, 
data JSON, 
PRIMARY KEY (id), 
FOREIGN KEY(tenant_id) REFERENCES tenants (id), 
FOREIGN KEY(type_id) REFERENCES form_types (id)
);
CREATE INDEX ix_forms_type_id ON forms (type_id);
CREATE INDEX ix_forms_tenant_id ON forms (tenant_id);
INSERT INTO tenants (name) VALUES ('mike'), ('bob');
INSERT INTO form_types (name) VALUES ('type 1'), ('type 2');
INSERT INTO forms (tenant_id, type_id, data) VALUES 
(1, 1, '{"string": "unicorns", "int": 1}'),
(1, 1, '{"string": "pythons", "int": 2}'),
(1, 1, '{"string": "pythons", "int": 8}'),
(1, 1, '{"string": "penguins"}');
CREATE OR REPLACE VIEW foo AS 
SELECT forms.id AS forms_id,
json_string(forms.data, 'string') AS "data.string",
json_int(forms.data, 'int') AS "data.int"
FROM forms 
WHERE forms.tenant_id = 1 AND forms.type_id = 1;
CREATE INDEX "forms_string" ON forms (json_string(data, 'string')) 
WHERE tenant_id = 1 AND type_id = 1;
CREATE INDEX "forms_int" ON forms (json_int(data, 'int')) 
WHERE tenant_id = 1 AND type_id = 1;
EXPLAIN ANALYZE VERBOSE SELECT "data.string" from foo;

输出：

Index Scan using forms_int on public.forms

(cost=0.13..8.40 rows=1 width=32) (actual time=0.085..0.239 rows=20 loops=1)
Output: json_string(forms.data, 'string'::text)
Total runtime: 0.282 ms

无enable_seqscan=off:

Seq Scan on public.forms  (cost=0.00..1.31 rows=1 width=32) (actual time=0.080..0.277 rows=28 loops=1)
Output: json_string(forms.data, 'string'::text)
Filter: ((forms.tenant_id = 1) AND (forms.type_id = 1))
Total runtime: 0.327 ms

d forms打印

Table "public.forms"
Column   |  Type   |                     Modifiers

-----------+---------+----------------------------------------------------
id        | integer | not null default nextval('forms_id_seq'::regclass)
tenant_id | integer | 
type_id   | integer | 
data      | json    | 
Indexes:
"forms_pkey" PRIMARY KEY, btree (id)
"forms_int" btree (json_int(data, 'int'::text)) WHERE tenant_id = 1 AND type_id = 1
"forms_string" btree (json_string(data, 'string'::text)) WHERE tenant_id = 1 AND type_id = 1
"ix_forms_tenant_id" btree (tenant_id)
"ix_forms_type_id" btree (type_id)
Foreign-key constraints:
"forms_tenant_id_fkey" FOREIGN KEY (tenant_id) REFERENCES tenants(id)
"forms_type_id_fkey" FOREIGN KEY (type_id) REFERENCES form_types(id)

Answer 1

指数与seqscan，成本

与机器的实际性能相比，您的random_page_cost似乎过高。随机I/O比Pg认为的更快(成本更低)，所以它选择了一个稍微不太理想的计划。

这就是为什么indexscan的成本估计是(cost=0.13..8.40 rows=1 width=32)，而seqscan的成本估计在(cost=0.00..1.31 rows=1 width=32)时略低。

降低random_page_cost-尝试SET random_page_cost = 2，然后重新运行。

要了解更多信息，请阅读有关PostgreSQL查询规划、参数和调优的文档，以及相关的wiki页面。

索引选择

PostgreSQL似乎在forms_int而不是forms_string上选择了索引扫描，因为它将是一个更紧凑、更小的索引，并且两个索引都与视图的搜索标准完全匹配：tenant_id = 1 AND type_id = 1。

如果禁用或放弃forms_int，它可能会使用forms_string，速度会稍微慢一点。

要理解的关键是，虽然索引中确实包含感兴趣的值，但PostgreSQL实际上并没有使用它。它在没有索引条件的情况下扫描索引，因为索引中的每个元组都匹配，以便从堆中获取元组。然后，它从这些堆元组中提取值并输出它们。

这可以用常数上的表达式索引来证明：

CREATE INDEX "forms_novalue" ON forms((true)) WHERE tenant_id = 1 AND type_id = 1;

PostgreSQL很可能会为查询选择这个索引：

regress=# EXPLAIN ANALYZE VERBOSE SELECT "data.string" from foo;
QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
Index Scan using forms_novalue on public.forms  (cost=0.13..13.21 rows=4 width=32) (actual time=0.190..0.310 rows=4 loops=1)
Output: json_string(forms.data, 'string'::text)
Total runtime: 0.346 ms
(3 rows)

所有的索引都是相同的大小，因为它们都很小，适合最小的分配：

regress=# SELECT x.idxname, pg_relation_size(x.idxname) FROM (VALUES ('forms_novalue'),('forms_int'),('forms_string')) x(idxname);
idxname    | pg_relation_size 
---------------+------------------
forms_novalue |            16384
forms_int     |            16384
forms_string  |            16384
(3 rows)

但由于行宽度较窄，novalue的统计数据将更具吸引力。

索引扫描与仅索引扫描

听起来你真正期望的是一个仅限索引的扫描，其中Pg从不接触表的堆，只使用索引中的元组。

我希望forms_string可以满足此查询的要求，但无法让Pg为其选择仅索引的扫描计划。

我还不清楚为什么Pg没有在这里使用仅索引扫描，因为它应该是一个候选者，但它似乎无法计划一个。如果我强制enable_indexscan = off，它将选择一个较差的位图索引扫描计划，如果强制禁用enable_bitmapscan，它将回落到最大成本估计seqscan。即使在感兴趣的表的VACUUM之后也是如此。

这意味着它不能在查询规划器中被生成为候选路径——Pg不知道如何对此查询使用仅索引扫描，或者认为由于某种原因无法这样做。

这不是视图内省的问题，因为扩展的视图查询是一样的。

Answer 2

您的表中数据不足。简而言之，当表适合单个磁盘页面时，Postgres不会使用索引。曾经当你的表包含几百或几千行时，它会变得太大而无法容纳，然后你会看到Postgres开始在相关的时候使用索引扫描。

需要考虑的另一点是，在进行大型导入后，需要analyze表。如果你的实际数据没有准确的统计数据，Postgres可能会认为一些索引扫描太贵而不屑一顾，而事实上它们很便宜。

最后，在某些情况下，比不使用索引更便宜。本质上，每当Postgres要以随机顺序重复访问大多数磁盘页面以检索大量行时，它都会认真考虑按顺序访问大多数(位图索引)或所有(seq扫描)磁盘页面一次并过滤掉无效行的成本。如果选择了足够多的行，则后者获胜。