sql - 将 2 个 GIN 索引与 gin_trgm_ops 合并为一个 | Postgres

Question

我有一张流动的 table

 create table mytable
(
id       serial     not null primary key,
text_id                      varchar(20)              not null,
customer_unit_id              text,
)

text_id 和 customer_unit_id 列主要存储 K-2021-8524 等代码。

在 Web 界面中，我使用搜索窗口按 text_id 或 customer_unit_id 过滤条目。

典型的搜索是这样的

SELECT id, text_id
FROM mytable
WHERE UPPER(text_id::text) LIKE UPPER('%2021-8%')
OR UPPER(customer_unit_id ::text) LIKE UPPER('%176%')

我已经为 GIN 创建索引以方便搜索

CREATE INDEX IF NOT EXISTS trgrm_test_text ON mytable
USING gin (Upper(text_id) gin_trgm_ops);

CREATE INDEX IF NOT EXISTS trgrm_test_customer ON mytable
USING gin (Upper(customer_unit_id ) gin_trgm_ops);

我试图制作 2 列 GIN 索引，以便有一个组合索引而不是 2 个单独的索引 - 但它不起作用(seq 扫描)

问题——对于这种类型的查询，是否可以使用一个组合索引而不是两个单独的索引？？？

谢谢……

PostgreSQL 版本 -11

Answer 1

另一种方法是创建第三列，其中包含 tsvector使用两列的内容然后对其进行索引，例如:

ALTER TABLE mytable ADD COLUMN ts tsvector;
UPDATE mytable SET ts =  to_tsvector('english',text_id||' '||customer_unit_id);

CREATE INDEX trgrm_ts_vec_idx2 ON mytable USING gin (ts);

你可以这样查询:

SELECT id, text_id
FROM mytable
WHERE 
 ts @@ to_tsquery('english', 'k-2021-8 | 176')

如果您负担不起额外的柱子，您可以使用 tsvector只在索引中，但它会使查询有点困惑，而且它应该也有点慢:

CREATE INDEX IF NOT EXISTS trgrm_ts_vec_idx ON mytable 
USING gin (to_tsvector('english', text_id || ' ' || customer_unit_id));

然后这样查询:

SELECT id, text_id
FROM mytable
WHERE 
 to_tsvector('english', text_id || ' ' || customer_unit_id) @@ 
 to_tsquery('english', 'k-2021-8 | 176');

演示: db<>fiddle