将两个GIN索引与gin_trgm_ops合并为一个| Postgres

Question

我有一个流动的表

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扫描)

问题-是否有可能有一个组合索引，而不是2个单独的从这种确切类型的查询??

谢谢…

PostgreSQL version -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');

Demo:db<>fiddle