Combine 2 GIN indexes with gin_trgm_ops into one | Postgres

user_11251373 0
Viewed 383

I have a flowing table

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

Columns text_id and customer_unit_id basically store codes like K-2021-8524 and similar.

In a web interface I use search window to filter out entries by text_id or customer_unit_id.

Typical search would be like this

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

I have created to GIN indexes to facilitate search

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);

I have tried to make 2 column GIN index in order to have one combined index instead of 2 separate - but it doesn’t work (seq scan)

Question – is it possible to have one combined index instead of 2 separate ones fro this exact type of queries???

Thanks….

PostgreSQL version -11

full-text-search postgresql sql
1 Answers

An alternative would be to create a third column containing a tsvector with the contents of both columns and then index it, e.g.:

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);

And you'd be able to query it like this:

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

In case you cannot afford an extra column, you can use the tsvector only in the index, but it would make queries a little confusing and it should be also a bit slower:

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

And query it like this:

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

user_22753880
Related