主页 / dba / 问题 / 240518
Accepted
Arya
Asked: 2019-06-14 10:24:59 +0800 CST

postgresql 上 ILIKE 的索引，性能不一致

  • 772

我有一个大约有 6400 万行的表，我在address_url列上创建了以下索引

CREATE INDEX trgm_idx_address_link
    ON public.address_link USING gin
    (address_url COLLATE pg_catalog."default" gin_trgm_ops)
    TABLESPACE pg_default;

服务器现在没有任何负载。我正在测试like的性能。

假设我们有以下查询

SELECT *
FROM "address_link"
WHERE "address_url" ILIKE '%eden-hill%'
LIMIT 1

有时，如果我第一次运行它，可能需要 10 秒。如果我再次运行它，它将在 0.195 秒内完成

然后我在同一列上创建了一个 btree 索引，看看是否有帮助

CREATE INDEX address_link_address_url
    ON public.address_link USING btree
    (address_url COLLATE pg_catalog."default")
    TABLESPACE pg_default;

但这并没有太大帮助。有时我第一次搜索关键字需要 2 秒，第二次需要 0.02 秒。我能做些什么来使性能更加一致吗？

postgresql postgresql-11

2 个回答

  1. Best Answer

    需要根据 LIKE 模式中存在的三元组检查索引的不同部分，然后需要检查表的不同部分以重新检查模式是否存在误报，并获取您选择的其他列为真积极的一面。

    对于给定模式的第一次，它需要从磁盘中获取大量索引和表的那些部分。连续第二次使用相同的模式，该数据已经从前一次缓存在内存中，因此会快得多。

    可以通过执行explain (analyze, buffers)查询来查看其详细信息，尤其是使用set track_io_timing=on

    如果您有足够的 RAM 将整个索引和整个表都缓存到 RAM 中，那么您可以使用pg_prewarm将它们全部预先放入内存中，这样您就不必零碎地做。

    • 2

  2. 要加速“喜欢”查询，您可以使用varchar_pattern_ops（或text_pattern_ops）索引。

    这是语法：

    CREATE INDEX address_link_address_url_like
    ON public.address_link (address_url varchar_pattern_ops);

    您可能会发现此文档页面很有用。

    • 1

相关问题