在Postgres中，是否可以临时禁用索引？

Question

在Postgres中，是否可以临时禁用索引？

performancedebuggingpostgresqlindexing

71

我有一张表上的一个索引，我想暂时禁用它，但是我找不到任何文件说明它是否可能被禁用。

原因：我有一个索引，可能会在与其设计用于加速查询无关的查询中出现问题。这是一个新索引，自从引入它后整个系统似乎变得更慢了。我只想能够可靠地将其排除为罪犯，并且这似乎是最简单的方法，其他解决方案建议以及更好的问题建议也欢迎。

- quodlibetor

3个回答

56

begin;
drop index foo_ndx;
explain analyze select * from foo;
rollback;

我认为没有办法只禁用其中一个索引，但你可以在事务中执行此操作，以使从中恢复变得非常简单。您还可以禁用 indexscan 以禁用所有索引。

此外，请确保在查询上执行 explain analyze。

- Seth Robertson

3

谢谢。不幸的是，我的问题是我不知道哪些查询被减慢了：我为其安装索引的所有内容都非常迅速，只是整个系统出了问题。因此，我需要全局禁用索引几分钟，而不仅仅是在一个事务内部禁用。 - quodlibetor

1

给你点赞，因为这是单个查询的正确方法。如果不是因为我的问题太笼统了，你本来就可以解决它了。 - quodlibetor

使用“EXPLAIN ANALYZE”来开始弄清楚正在发生什么是正确的建议。然后你可以说：“为什么存在一个'INDEX'会导致'index_scan'的时间从X变为Y？” - Sean

如果你不知道哪些查询很慢，那就开启长时间运行语句的日志并查看。 - Scott Marlowe

+1 这是一个有效的答案（因为问题标题有点模糊），而且正是我需要的。聪明的技巧。（你也可以用主键来做到这一点，只需使用 ALTER TABLE foo DROP CONSTRAINT foo_pkey; 而不是 DROP INDEX foo_ndex;。） - cdhowie

3

我已经阅读了这篇文章，它将在 foo_ndx 上获得一个 ACCESS EXCLUSIVE 锁，那么这会对其他并发查询产生什么影响呢？它们会使用或不使用该索引，还是会被阻塞直到该事务完成？ - Steve Kehlet

2

我已经创建（并审核）了一个函数，可以应对各种情况。你需要小心使用它，但它确实有效。

CREATE OR REPLACE PROCEDURE global.enable_indexes(
    IN schema_name TEXT
,   IN table_name TEXT
,   IN index_name TEXT
,   IN enable_use BOOLEAN
,   IN enable_updates BOOLEAN
,   IN skip_essentials BOOLEAN DEFAULT TRUE
) AS
$$
DECLARE
    each_record RECORD;
BEGIN

/*
USAGE:

schema_name:
- filters down by schema name
- if NULL then does not filter
table_name:
- filters down by table name (careful - not schema qualified)
- if NULL then does not filter
index_name:
- filters down by index name (careful - not schema qualified)
- if NULL then does not filter
enable_use:
- This SETs the index as being available for use.
- If enable_updates is FALSE, then automatically FALSE
enable_updates:
- This SETs the index to be updated (which doesn't imply that it is enabled for use)
- When this was previously FALSE for the given index, then when setting to true will also trigger a rebuild of the index
skip_essentials:
- When this is true, PRIMARY and UNIQUE indexes will not be included in the scope (no changes).
- Optional
*/


IF array_replace(ARRAY[schema_name, table_name, index_name], NULL, '') <@ ARRAY[''] THEN
    RAISE EXCEPTION 'Error: Must specify at least one of schema_name | table_name | index_name';
END IF;

IF enable_updates IS FALSE THEN
    enable_use := FALSE;
    RAISE INFO 'FYI: Because enable_updates is FALSE, then likewise enable_use must be as well';
END IF;

FOR each_record IN
    select
        indexrelid
    ,   (schemaname||'.'||indexname) as index_name
    ,   indisvalid
    ,   indisready
    ,   (schemaname||'.'||tablename) as table_name
    ,   (CASE WHEN indisready IS FALSE and enable_use IS TRUE AND enable_updates IS TRUE THEN TRUE ELSE FALSE END) as needs_rebuilding
    FROM pg_indexes, pg_index
    WHERE
        indexrelid = (schemaname||'.'||indexname)::regclass
    AND case when schema_name <> '' THEN schemaname = schema_name ELSE TRUE END
    AND case when table_name <> '' THEN tablename = table_name ELSE TRUE END
    AND case when index_name <> '' THEN indexname = index_name ELSE TRUE END
    AND case when true THEN least(indisprimary, indisunique) = FALSE ELSE TRUE END
    AND case when skip_essentials THEN least(indisprimary, indisunique) = FALSE ELSE TRUE END
LOOP
    BEGIN

    RAISE INFO 'Set index % to have use % and updates %.'
        ,   each_record.index_name
        ,   (case when each_record.indisvalid AND enable_use THEN 'enabled (not changed)' WHEN NOT each_record.indisvalid AND enable_use THEN 'enabled (changed)' else 'disabled' END)
        ,   (case when each_record.indisready AND enable_updates THEN 'enabled (not changed)' WHEN NOT each_record.indisready AND enable_updates THEN 'enabled (changed)' else 'disabled' END)
    ;

    UPDATE pg_index
    SET
        indisvalid = enable_use
    ,   indisready = enable_updates
    WHERE
        indexrelid = each_record.indexrelid
    ;

    IF each_record.needs_rebuilding THEN
        RAISE INFO '... Reindexing and Analyzing %', each_record.index_name;
        EXECUTE format('REINDEX INDEX %1$s; ANALYZE %2$s;', each_record.index_name, each_record.table_name);
    END IF;

    COMMIT;

    END;

END LOOP;

END
$$
LANGUAGE plpgsql
;

- Alexi Theodore

该死的程序！我现在有\d tags没有显示索引（这是在运行call enable_indexes('', 'tags', '', true, true, false);和call enable_indexes('', 'tags', '', false, false, false);之前）。我还将pg_catalog.pg_class.relhasindex的值设置为f，其中relname ='tags'。我已经更新了relhasindex ='t'，但tags上的索引仍未显示。现在我正在运行reindex table tags。 - porton

重新索引表tags；显然解决了问题。 - porton

该过程必须在主循环完成后，针对整个表执行一次ANALYZE而不是每个索引执行一次。 - Sergey Nevmerzhitsky

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- araqnid · Accepted Answer

您可以操纵系统目录以禁用索引：

update pg_index set indisvalid = false where indexrelid = 'test_pkey'::regclass

这意味着该索引不会用于查询，但仍将被更新。它是用于并发索引构建的标志之一。请注意，我只进行了快速测试以查看索引是否仍在更新，买方自负。