Use few analyzers in GIN index in Postgres

Question

I want to create GIN index for Postges full text search and I would like to ask is it possible if I store analyzer name for each row in table in separate column called lang, use it to create GIN index with different analyzer for each row taken from this field lang?

This is what I use now. Analyzer – ‘english’ and it is common for each row in indexed table.

CREATE INDEX CONCURRENTLY IF NOT EXISTS 
decription_fts_gin_idx ON api_product 
USING GIN(to_tsvector('english', description))

I want to do something like this:

CREATE INDEX CONCURRENTLY IF NOT EXISTS 
decription_fts_gin_idx ON api_product 
USING GIN(to_tsvector(api_product.lang, description))

( it doesnt work)

in order to retrieve analyzer configuration from field lang and use its name to populate index.

Is it possible to do it somehow or it is only possible to use one analyzer for the whole index?

DDL, just in case..

-- auto-generated definition
create table api_product
(
    id                       serial       not null
        constraint api_product_pkey
            primary key,
    name                     varchar(100) not null,
    standard                 varchar(40)  not null,
    weight                   integer      not null
        constraint api_product_weight_check
            check (weight >= 0),
    dimensions               varchar(30)  not null,
    description              text         not null,
    textsearchable_index_col tsvector,
    department               varchar(30)  not null,
    lang                     varchar(25)  not null
);

alter table api_product
    owner to postgres;

create index textsearch_idx
    on api_product (textsearchable_index_col);

Query to run for seach:


                SELECT *,
                ts_rank_cd(to_tsvector('english', description),
                to_tsquery('english', %(keyword)s), 32) as rnk 
                FROM api_product 
                WHERE to_tsvector('english', description) @@ to_tsquery('english', %(keyword)s)
                ORDER BY rnk DESC, id

where 'english' would be changed to 'lang' field analyzer name (english, french, etc)

If you could create the index you wanted, how would you use it? What query do you envision running? — jjanes
– jjanes, Commented Oct 15, 2019 at 14:48

jjanes · Accepted Answer · 2019-10-15 14:59:18Z

3

If you know ahead of time the language you are querying against, you could create a series of partial indexes:

CREATE INDEX CONCURRENTLY ON api_product 
USING GIN(to_tsvector('english', description)) where lang='english';

Then in your query you would add the language you are searching in:

 SELECT *,
            ts_rank_cd(to_tsvector('english', description),
            to_tsquery('english', %(keyword)s), 32) as rnk 
            FROM api_product 
            WHERE to_tsvector('english', description) @@ to_tsquery('english', %(keyword)s)
            and lang='english'
            ORDER BY rnk DESC, id

answered Oct 15, 2019 at 14:59

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aleksei Khatkevich Over a year ago

Thanks for the answer.

Jeremy · Accepted Answer · 2019-10-15 17:30:28Z

3

What you asked about is definitely possible, but you have the wrong type for the lang column:

create table api_product(description text, lang regconfig);
create index on api_product using gin (to_tsvector(lang, description));
insert into api_product VALUES ('the description', 'english');

answered Oct 15, 2019 at 17:30

Jeremy

6,77322 silver badges21 bronze badges

Collectives™ on Stack Overflow

Use few analyzers in GIN index in Postgres

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related