Postgresql Multiple index with USING gin

Question

I have to query for example zips_of_state,state_names,county_names from a state table

select  zips_of_state,state_names,county_names 
from state 
where zips_of_state='something' 
   OR state_names='something' 
   OR county_names ='something';

Now I am indexing as

CREATE INDEX ind_zips 
   ON state 
   USING gin(to_tsvector('english', zips_of_state)); -- this works

But how do I do multi indexing like

CREATE INDEX ind_zips 
   ON state 
   -- this doesn't work
   USING gin(to_tsvector('english', zips_of_state)),state_names, county_names ;

Could you try CREATE INDEX ind_zips ON state USING gin(to_tsvector('english', zips_of_state),state_names, county_names);? — zedfoxus
– zedfoxus, Commented May 18, 2020 at 16:46
@zedfoxus it doesnt work - ERROR: data type character varying has no default operator class for access method "gin" — Deepak
– Deepak, Commented May 19, 2020 at 5:54

Laurenz Albe · Accepted Answer · 2020-05-20 07:09:51Z

1

That query doesn't match the index at all.

If you don't want full text search, you'll need a regular B-tree index on each of the columns individually, so that you can get a bitmap or.

If you want full text search, write the query as

WHERE to_tsvector('english', zips_of_state) ||
      to_tsvector('english', state_names) ||
      to_tsvector('english', country_names)
   @@ to_tsquery('english', 'something')

anf use a GIN index on the expression on the left side of the @@ operator:

CREATE INDEX ON stat USING gin ((
   to_tsvector('english', zips_of_state) ||
   to_tsvector('english', state_names) ||
   to_tsvector('english', country_names)
));

edited May 20, 2020 at 7:09

answered May 18, 2020 at 19:01

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

Deepak Over a year ago

Tried this but didnt work. the query time does not see any improvement.

Laurenz Albe Over a year ago

I have spelled out the CREATE INDEX. Is that what you tried?

Collectives™ on Stack Overflow

Postgresql Multiple index with USING gin

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related