postgresql - Postgresql 规划器不会为“NOT”查询选择索引

Question

[标题更新以反映描述中的更新]

我正在运行 Postgresql 9.6

我有一个复杂的查询，它没有使用我期望的索引，当我把它分解成这个小例子时，我不知道为什么没有使用索引。

这些示例在具有 100 万条记录的表上运行，当前所有记录的列状态值为“COMPLETED”。状态是一个文本列，我有一个 btree 索引。

以下查询按我的预期使用我的索引：

explain analyze
SELECT * FROM(
    SELECT
    q.state = 'COMPLETED'::text AS completed_successfully
    FROM request.request q
) a where NOT completed_successfully;

五

                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using request_state_index on request q  (cost=0.43..88162.19 rows=11200 width=1) (actual time=200.554..200.554 rows=0 loops=1)
   Filter: (state <> 'COMPLETED'::text)
   Rows Removed by Filter: 1050005
   Heap Fetches: 198150
 Planning time: 0.272 ms
 Execution time: 200.579 ms
(6 rows)

但是，如果我在引用我的表的选择中添加任何其他内容，那么规划器会选择执行顺序扫描。

explain analyze
SELECT * FROM(
    SELECT
    q.state = 'COMPLETED'::text AS completed_successfully,
    q.type
    FROM request.request q
) a where NOT completed_successfully;

五

                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Seq Scan on request q  (cost=0.00..234196.06 rows=11200 width=8) (actual time=407.713..407.713 rows=0 loops=1)
   Filter: (state <> 'COMPLETED'::text)
   Rows Removed by Filter: 1050005
 Planning time: 0.113 ms
 Execution time: 407.733 ms
(5 rows)

即使这个更简单的例子也有同样的问题。

用途指数：

SELECT
q.state
FROM request.request q
WHERE q.state = 'COMPLETED';

不使用索引：

SELECT
q.state, 
q.type
FROM request.request q
WHERE q.state = 'COMPLETED';

[更新]我现在明白（对于这种情况）它正在使用的索引只有 INDEX，在这种情况下它将停止使用它，因为类型也不在索引中。所以问题可能是它为什么不在下面的“不”情况下使用它：

当我使用不在表中的不同值时，我知道使用索引（这是有道理的）：

SELECT
q.state, 
q.type
FROM request.request q
WHERE q.state = 'CREATED';

但如果我不这样做，它不会：

SELECT
q.state, 
q.type
FROM request.request q
WHERE q.state != 'COMPLETED';

为什么我的索引没有被使用？

我能做些什么来确保它被使用？

大多数时候，我希望该表中的几乎所有记录都处于许多最终状态之一（使用 IN 运算符）；。因此，当运行我的更复杂的查询时，我希望这些记录应该尽早并快速地从查询的更昂贵的部分中排除。

[更新]

看起来“NOT”不是受支持的 B-Tree 操作。我需要某种独特的方法：https ://www.postgresql.org/docs/current/indexes-types.html#INDEXES-TYPES-BTREE

我尝试添加以下部分索引，但它们似乎不起作用：

CREATE INDEX request_incomplete_state_index ON request.request (state) WHERE state NOT IN('COMPLETED', 'FAILED', 'CANCELLED');
    
CREATE INDEX request_complete_state_index ON request.request (state) WHERE state IN('COMPLETED', 'FAILED', 'CANCELLED');

此部分索引确实有效，但不是理想的解决方案。

CREATE INDEX request_incomplete_state_exact_index ON request.request (state) WHERE state != 'COMPLETED';

explain analyze SELECT q.state, q.type FROM request.request q WHERE q.state != 'COMPLETED';

我也试过这个表达式索引，虽然也不理想也没有奏效：

CREATE OR REPLACE FUNCTION request.request_is_done(in_state text)
 RETURNS BOOLEAN
 LANGUAGE sql
 STABLE
AS $function$
    SELECT in_state IN ('COMPLETED', 'FAILED', 'CANCELLED');
$function$
;

CREATE INDEX request_is_done_index ON request.request (request.request_is_done(state));

explain analyze select * from request.request q where NOT request.request_is_done(state);

使用具有 equals 的状态列表（在子句中）。所以我可能必须弄清楚我更大的查询才能不使用NOT。

标签： postgresqlindexingdatabase-indexesb-tree-index