How to use gin indexes in postgresql

Question

I have never used GIN indexing before and I am trying to understand how it works. I have a static table with 4million rows, which I need to run SELECT queries with WHERE clauses.

Right now my table has a B-Tree index on the column "name" which is used in the where clause.

I have read that doing a full text search using a GIN index can greatly improve the speed. I have read the tutorial here and right now I am not sure if this is something I can use when I have only one table.

Does full text search works in case of multiple tables that we want to join?

I understand that the GIN Indexing works on a tsvector column. This is what I have done till now:

I have altered my table and added one more column: tsv:

ALTER TABLE my_table ADD COLUMN tsv tsvector;

I createad a GIN index on my ts_vector column:

CREATE INDEX tsv_idx ON my_table USING gin(to_tsvector('english', name));

And I have updated the column:

UPDATE my_table SET tsv = to_tsvector(name)

Are the above correct? So now if I want to run the following query:

SELECT clm1, clmn2 FROM my_table WHERE name LIKE 'Ath%' ORDER BY 1

how can I do it using the GIN index?

I tried this:

SELECT clm1, clmn2 FROM my_table WHERE tvs LIKE 'Ath%' ORDER BY 1

But I get an error:

ERROR:  operator does not exist: tsvector ~~ unknown

What am I missing here? What is the correct use of GIN? And can I apply full text search on a single table?

Chris Travers · Accepted Answer · 2017-01-05 09:50:50Z

2

You need to index for what you want to query. A tsvector doesn't help you with a like search. Since that is a prefix though, a standard btree would work. If you want to do an infix search (where mycol like '%something%') you need to use pg_trgm extension and make a trigram GIN index.

Pay close attention to what to_tsvector returns and note that is what you are indexing. What you are indexing doesn't give your query anything.

answered Jan 5, 2017 at 9:50

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

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

2 Comments

user1919 Over a year ago

Thanks. After all I used the pg_trgm extension together with the GIN index. I have created the index as: CREATE INDEX my_index ON my_table USING gin (name COLLATE pg_catalog."default" gin_trgm_ops); Using the EXPLAIN command made sure that the index is used and there was significan improvement in the speed.

user1919 Over a year ago

Now I am trying to figure out other ways to speed things up. E.g. materialized views or server side cursors (as i use psycopg2).

Collectives™ on Stack Overflow

How to use gin indexes in postgresql

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