Best indexing strategy for several varchar columns in Postgres

Question

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!

Answer 1

1. "find out what sets of columns are queried together frequently and create indexes for them" is the best approach.

   Also, order the columns in indexes based on 2 criteria:

   • Columns that are more frequent in where clauses have priority

   • Columns that are more selective have priority.

     E.g. if "where col1=val1" returns an average of 2 rows for a random value val1, but "where col2=val2" returns 2000 rows on average, col1 should have priority as far as being early in index

2. Not sure about Postgres, but in Sybase, the like '%xxx%' expressions (e.g match value starts with wildcard) do NOT hit the index, EVER. So the index can't help with that query unless you select those 3 columns and thus make it a covering index.

Answer 2

Any query filter conditions that prefix with a wildcard cannot utilise an index [suffixing is fine, i.e. 'abc%']

Answer 3

PostgreSQL will use indexes if the columns queried are in the same order in the where clause and the index. An index of all 10 columns would work fine.

However, you will get no benefits from an index if you have wildcard matching on both sides of the queried text.

It will make more sense to use the full text module for PostgreSQL to generate your indexes if you need wildcards on both sides of user supplied input.

http://www.postgresql.org/docs/8.3/static/textsearch.html is a good place to start.