postgresql - Postgres 在查询索引表达式 View 时使用错误的索引？

Question

当我使用 Postgres 9.3 运行以下脚本(将 enable_seqscan 设置为 off)时，我希望最终查询能够使用“forms_string”部分索引，而是使用“forms_int”索引，这是没有意义的。

当我使用 JSON 函数和更多类型的索引的实际代码对此进行测试时，它似乎始终对每个查询使用最后创建的索引。

添加更多不相关的行，以便与部分索引相关的行仅占表中总行的一小部分，从而导致“位图堆扫描”，但此后仍然提到相同的不正确索引。

知道如何让它使用正确的索引吗？

CREATE EXTENSION IF NOT EXISTS plv8;<p></p>

<p>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;</p>

<p>$$ LANGUAGE plv8 IMMUTABLE STRICT;</p>

<p>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;</p>

<p>CREATE TABLE form_types (
    id SERIAL NOT NULL, 
    name VARCHAR(200), 
    PRIMARY KEY (id)
);</p>

<p>CREATE TABLE tenants (
    id SERIAL NOT NULL, 
    name VARCHAR(200), 
    PRIMARY KEY (id)
);</p>

<p>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)
);</p>

<p>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"}');</p>

<p>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;</p>

<p>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;</p>

<p>EXPLAIN ANALYZE VERBOSE SELECT "data.string" from foo;
</p>

输出:

 Index Scan using forms_int on public.forms<br/>
   (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<br/>
-----------+---------+----------------------------------------------------
 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 认为的更快(成本更低)，因此它选择了一个稍微不太理想的计划。

这就是为什么索引扫描的成本估算为 (cost=0.13..8.40 rows=1 width=32) 而 seqscan 的成本估算略低，为 (cost=0.00.. 1.31 行=1 宽度=32)。

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

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

索引选择

PostgreSQL 似乎选择对 forms_int 而不是 forms_string 进行索引扫描，因为它将是一个更紧凑、更小的索引，并且两个索引都与搜索条件完全匹配对于 View :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 它将回退到最大成本估计序列扫描。即使在对感兴趣的表进行VACUUM之后也是如此。

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

这不是 View 自省(introspection)的问题，因为扩展 View 查询是相同的。