Home PostgreSQL fulltext search index
Reply: 1

PostgreSQL fulltext search index

Kanarsky Published in 2018-02-13 16:49:50Z

I have a table with text fields and I need efficient full text search. I found out some technics and I wonder is it better to extend schema by creating additional ts_vector field based on text fields or i can save my schema and just create index this way:

CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', title || ' ' || body));

Is there any difference in the sense of efficiency?

a_horse_with_no_name Reply to 2018-02-13 16:55:34Z

Both strategies will work. If you just have the index, you need to make sure that queries use the exact same expression in the where clause as the one in the index, otherwise the index will not be used. E.g. where ts_vector('english', title) .... will not use that index.

The computed column with the ts_vector() will require triggers to be updated automatically because Postgres does not (yet) have persisted calculated columns. So the index "only" solution might be faster when it comes to updating the able as it removes the overhead of the trigger.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.300148 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO