如何在具有多个 OR 的 postgres 中为文本搜索查询构建索引

Question

外部连接器在我的 postgres 数据库上创建了一个非常糟糕的请求。但这是一项业务需求，不幸的是，我无法更改请求。

请求如下所示

SELECT avantage.id
FROM avantage 
WHERE (CAST(avantage.web_prepaye_cda_codepin AS TEXT) LIKE '%' || 'searchword' || '%') = true
OR (CAST(avantage.type AS TEXT) LIKE '%' || 'searchword' || '%') = true 
OR (CAST(avantage.statut AS TEXT) LIKE '%' || 'searchword' || '%') = true 
OR (CAST(avantage.soustype AS TEXT) LIKE '%' || 'searchword' || '%') = true 
OR (CAST(avantage.source AS TEXT) LIKE '%' || 'searchword' || '%') = true 
OR (CAST(avantage.prepaye_cda_codepin AS TEXT) LIKE '%' || 'searchword' || '%') = true 
OR (CAST(avantage.typedeclenchement__c AS TEXT) LIKE '%' || 'searchword' || '%') = true 
OR (CAST(avantage.receivedby__c AS TEXT) LIKE '%' || 'searchword' || '%') = true 
OR (CAST(avantage.givenby__c AS TEXT) LIKE '%' || 'searchword' || '%')  = true 
OR (CAST(avantage.numero_ticket AS TEXT) LIKE '%' || 'searchword' || '%') = true 
OR (CAST(avantage.numero_carte AS TEXT) LIKE '%' || 'searchword' || '%') = true 
OR (CAST(avantage.nom_avantage AS TEXT) LIKE '%' || 'searchword' || '%') = true 
OR (CAST(avantage.last_modified_by AS TEXT) LIKE '%' || 'searchword' || '%') = true 
OR (CAST(avantage.id_carte AS TEXT) LIKE '%' || 'searchword' || '%') = true 
OR (CAST(avantage.icu AS TEXT) LIKE '%' || 'searchword' || '%') = true 
OR (CAST(avantage.description AS TEXT) LIKE '%' || 'searchword' || '%') = true 
OR (CAST(avantage.account_id AS TEXT) LIKE '%' || 'searchword' || '%') = true 
OR (CAST(avantage.created_by AS TEXT) LIKE '%' || 'searchword' || '%') = true 
ORDER BY  avantage.id LIMIT 30 OFFSET 0;

我尝试在每个字段上添加 Gin 索引，如下所示：

CREATE INDEX trgm_avantage_web_prepaye_cda_codepin_idx
ON avantage
USING gin
(CAST(web_prepaye_cda_codepin AS TEXT)  COLLATE pg_catalog."default" gin_trgm_ops);
...
...

单独它工作得很好。

它适用于 5 到 7 个条件(取决于所选条件(。 但是在更多条件下，Postgres 停止使用索引并创建一个经典的非常长的过滤器查询：

Limit  (cost=0.09..217.47 rows=1 width=8) (actual time=15952.773..15952.773 rows=0 loops=1)
->  Index Scan using avantage_pkey on avantage  (cost=0.09..546295.48 rows=2513 width=8) (actual time=15952.771..15952.771 rows=0 loops=1)
Filter: (((web_prepaye_cda_codepin)::text ~~ '%searchword%'::text) OR ((type)::text ~~ '%searchword%'::text) OR ((statut)::text ~~ '%searchword%'::text) OR ((soustype)::text ~~ '%searchword%'::text) OR ((source)::text ~~ '%searchword%'::text) OR ((prepaye_cda_codepin)::text ~~ '%searchword%'::text) OR ((typedeclenchement__c)::text ~~ '%searchword%'::text) OR ((receivedby__c)::text ~~ '%searchword%'::text) OR ((givenby__c)::text ~~ '%searchword%'::text) OR ((numero_ticket)::text ~~ '%searchword%'::text) OR ((numero_carte)::text ~~ '%searchword%'::text) OR ((nom_avantage)::text ~~ '%searchword%'::text) OR ((last_modified_by)::text ~~ '%searchword%'::text) OR ((id_carte)::text ~~ '%searchword%'::text) OR ((icu)::text ~~ '%searchword%'::text) OR ((description)::text ~~ '%searchword%'::text) OR ((account_id)::text ~~ '%searchword%'::text) OR ((created_by)::text ~~ '%searchword%'::text))
Rows Removed by Filter: 8028920
Planning time: 0.922 ms
Execution time: 15952.814 ms

我不知道查询中可能的最大索引扫描是否有任何限制？ 有没有另一种方法可以在不接触查询的情况下优化查询？

Answer 1

尝试使用

ORDER BY avantage.id + 0

这样 PostgreSQL 就无法使用它所做的索引扫描，因为它估计了错误的结果数量。

Answer 2

我确定有一个限制，但你没有遇到它。 如果需要，PostgreSQL可以创建具有1000位图索引扫描的BitmapOr。我不知道你能比这高多少，但一个表中只能有 1600 列，这样就会对事情施加实际限制。

它最终会改变计划，仅仅是因为它认为(看起来是错误的(另一个会更快。 每个额外的 OR 都会使计划者认为它将处理结果中的更多行，这使得使用主键索引(以避免排序并启用提前停止(看起来越来越有吸引力。

如果您无法以任何方式触摸查询，您可以触摸什么？ 你能用set enable_indexscan = off; <this query>; reset enable_indexscan;包装查询吗

你能摆脱主键吗？ 如果它仅用于维护唯一性，而不是在查询或外键中，也许您可以将其替换为 (id+0( 上的 UNIQUE 索引。

也许您可以在全球范围内设置enable_indexscan，或者仅针对这一个用户(alter user user_name set enable_indexscan=off(？ 这不是很可取，但你在这里刮桶底。 大多数索引扫描只会转换为位图扫描，其性能不会有太大变化。 问题在于仅索引扫描也会被禁用，以及使用索引来实现排序，而位图扫描不是其中任何一个的有效替代品。 但也许你并不真正依赖这些东西。

还有一些方法可以使用连接池程序来拦截和重写查询。 也许你可以在那里使用类似的东西。 我没有使用过它们中的任何一个，所以不能推荐任何。 我知道pgbouncer-rr。