我的表有6.5亿行（根据我找到的here查询快速但相当精确的估计）。
它有一个名为receiver_account_id的文本列，我需要能够搜索如下记录：r.receiver_account_id LIKE '%otherWordsHere' .
因为我使用了前导通配符，所以这些搜索非常慢。我需要一个索引。根据here，我猜测我需要一个GIN索引。
我扑过去：
CREATE EXTENSION pg_trgm;
CREATE EXTENSION btree_gin;
CREATE INDEX CONCURRENTLY receipts_receiver_account_id_gin_idx ON public.receipts USING gin (receiver_account_id);但我不确定索引是否正在创建。
如果ran：
SELECT
now()::TIME(0),
a.query,
p.phase,
round(p.blocks_done / p.blocks_total::numeric * 100, 2) AS "% done",
p.blocks_total,
p.blocks_done,
p.tuples_total,
p.tuples_done,
ai.schemaname,
ai.relname,
ai.indexrelname
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a ON p.pid = a.pid
LEFT JOIN pg_stat_all_indexes ai on ai.relid = p.relid AND ai.indexrelid = p.index_relid;但我只是看到<insufficient privilege>（这很奇怪，因为我拥有这台机器）和一堆NULL。
我从here收到的下两个状态查询。SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;显示：
然后道：
SELECT a.datname,
l.relation::regclass,
l.transactionid,
l.mode,
l.GRANTED,
a.usename,
a.query,
a.query_start,
age(now(), a.query_start) AS "age",
a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE mode = 'ShareUpdateExclusiveLock'
ORDER BY a.query_start;显示：
这样做是否正确？如何知道索引创建何时完成？
2条答案
按热度按时间s71maibg1#
这里可以使用pg_trgm索引，但是它不必要的慢和大。最好是函数索引。
如果模式包含了用反斜杠转义的%或_，那么处理模式就需要比调用reverse（）更复杂。
mqkwyuun2#
定制的表达式索引
如果带有前导通配符的查询是该列上唯一（或唯一重要）的查询类型，那么考虑expression index，就像@jjanes建议的那样。它通常比trigram索引（小得多），维护成本也更低，而且适合查询的速度更快。（尽管它的通用性差得多！）
在Postgres的现代版本中，我倾向于使用
COLLATE "C"索引而不是text_pattern_ops。在你的问题中没有任何暗示，但是，通常情况下，你希望搜索不区分大小写。因此，我将
lower()添加到表达式中，得到：CONCURRENTLY。否则，不使用它会更快。匹配查询中的表达式：
请注意我是如何将通配符显式地连接到右边的。这使得Postgres即使对于带有参数化的 'otherWordsHere' 的通用查询计划也可以使用索引。
或者，更快地使用Postgres 15或更高版本中的“starts with”运算符
^@：没有通配符。也没有通用查询计划的障碍。请参阅：
使用
COLLATE "C"而不是text_pattern_ops的一个小缺点是，您必须在查询中拼写出COLLATE "C"来匹配索引。相关：
如果我们对模式'% otherWordsHere'中的
%部分和otherWordsHere部分有更多的了解，比如长度或常量位，我们也许可以进一步优化。尝试三元组索引失败
不需要额外的模块
btree_gin来为字符串类型的列创建三元组索引，只需要pg_trgm即可。但是您忘记了声明所需的操作符类：
您可能仍然希望该索引涵盖各种模式...
转义
LIKE模式中的特殊字符请参阅：
跟踪进度
如何知道索引创建何时完成？
从Postgres 12开始，您可以咨询
pg_stat_progress_create_index了解进度。请参见：