Postgresql Text Search Performance

Question

I have been looking into text search (without tsvector) of a varchar field (more or less between 10 to 400 chars) that has the following format:

field,field_a,field_b,field_c,...,field_n

The query I am planning to run is probably similar to:

select * from information_table where fields like '%field_x%'

As there are no spaces in fields, I wonder if there are some performance issues if I run the search across 500k+ rows. Any insights into this? Any documentation around performance of varchar and maybe varchar index?

I am not sure if tsvector will work on a full string without spaces. What do you think about this solution? Do you see another solutions that could help improve the performance?

Thanks and I look forward to hearing from you. R

Answer 1

In general the text search parser will treat commas and spaces the same, so if you want to use FTS, the structure with commas does not pose a problem. pg_trgm also treats commas and spaces the same, so if you want to use that method instead it will also not have a problem due to the commas.

The performance is going to depend on how popular or rare the tokens in the query are in the body of text. It is hard to generalize that based on one example row and one example query, neither of which looks very realistic. Best way to figure it out would be to run some real queries with real (or at least realistic) data with EXPLAIN (ANALYZE, BUFFERS) and with track_io_timing turned on.