Home How to use gin indexes in postgresql

# How to use gin indexes in postgresql

user1919
1#
user1919 Published in 2017-01-04 12:51:47Z
 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
2#
Chris Travers Reply to 2017-01-05 09:50:50Z
 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.
 You need to login account before you can post.
Processed in 0.337065 second(s) , Gzip On .