如何为前缀通配符搜索创建列索引并查看进度？

一、当前索引创建操作的问题 #

你创建GIN索引的语句存在关键错误：
直接对文本列receiver_account_id创建GIN索引无法支持LIKE '%xxx'这类后缀模糊查询，必须使用pg_trgm扩展提供的gin_trgm_ops操作符类，才能让GIN索引适配模糊匹配场景。正确的创建语句应该是：

CREATE INDEX CONCURRENTLY receipts_receiver_account_id_gin_idx 
ON public.receipts USING gin (receiver_account_id gin_trgm_ops);

另外，btree_gin扩展在这里是多余的——你不需要将B-tree索引特性与GIN结合，仅需pg_trgm即可满足需求。

二、如何确认索引创建进度及状态 #

1. 解决权限不足问题
   若执行进度查询返回<insufficient privilege>，说明当前用户缺失pg_monitor角色权限。联系数据库管理员赋予该权限后，即可用以下语句查看创建进度：

SELECT * FROM pg_stat_progress_create_index;

2. 无权限时的替代方法

• 查看pg_stat_activity中对应进程的状态：

  SELECT pid, query, state, now() - query_start AS duration
  FROM pg_stat_activity
  WHERE query LIKE '%CREATE INDEX CONCURRENTLY%receipts_receiver_account_id_gin_idx%';
  

  若状态为active且duration持续增长，说明索引仍在创建；若状态变为idle或进程消失，说明创建已完成或终止。
• 检查索引有效性：

  SELECT indexname, indisvalid
  FROM pg_indexes
  WHERE tablename = 'receipts' AND indexname = 'receipts_receiver_account_id_gin_idx';
  

  indisvalid为t表示索引有效且创建完成；为f则说明创建失败，索引处于无效状态，需删除后重新创建。

三、无效索引与锁的处理 #

1. 无效索引
   若查询显示存在无效索引，直接执行删除语句即可（CONCURRENTLY创建失败会留下无法正常使用的无效索引）：

DROP INDEX CONCURRENTLY public.receipts_receiver_account_id_gin_idx;

2. ShareUpdateExclusiveLock锁
   使用CREATE INDEX CONCURRENTLY时，PostgreSQL会持有ShareUpdateExclusiveLock，该锁不会阻塞表的读写操作，但如果有其他长时间持有同类锁的进程（如ALTER TABLE、VACUUM FULL等），会导致索引创建被阻塞。可通过以下语句查看持有锁的进程：

SELECT pid, usename, query, mode
FROM pg_locks
WHERE relation = 'receipts'::regclass AND mode = 'ShareUpdateExclusiveLock';

若为无关进程持有锁，可联系管理员谨慎终止该进程；若为索引创建进程自身持有锁，说明创建仍在进行中。

内容的提问来源于stack exchange，提问作者Ryan