Best indexing strategy for several varchar columns in Postgres
- by Corey
I have a table with 10 columns that need to be searchable (the table itself has about 20 columns). So the user will enter query criteria for at least one of the columns but possibly all ten. All non-empty criteria is then put into an AND condition
Suppose the user provided non-empty criteria for column1 and column4 and column8 the query would be:
select * from the_table
where column1 like '%column1_query%'
and column4 like '%column4_query%'
and column8 like '%column8_query%'
So my question is: am I better off creating 1 index with 10 columns? 10 indexes with 1 column each? Or do I need to find out what sets of columns are queried together frequently and create indexes for them (an index on cols 1,4 and 8 in the case above).
If my understanding is correct a single index of 10 columns would only work effectively if all 10 columns are in the condition.
Open to any suggestions here, additionally the rowcount of the table is only expected to be around 20-30K rows but I want to make sure any and all searches on the table are fast.
Thanks!
