Best indexing strategy for multiple varchar columns in Postgres

Question

I have a table with 10 columns that should be searchable (the table itself contains about 20 columns). Thus, the user will enter query criteria for at least one of the columns, but possibly all ten. All non-empty criteria are then placed in the AND condition.

Suppose the user is provided with non-empty criteria for columns1 and column4 and column8, the query will look like this:

select * from the_table where column1 like '%column1_query%' and column4 like '%column4_query%' and column8 like '%column8_query%' 

So my question is: should I create 1 index with 10 columns? 10 indexes with 1 column each? Or I need to find out which groups of columns are often set together and create indexes for them (index in columns 1,4 and 8 in the above case).

If my understanding is correct, one index of 10 columns will work only if all 10 columns are in state.

Open all the sentences here, in addition, the number of rows in the table should be about 20-30K rows, but I want all and all queries in the table to be fast.

Thanks!

Answer 1

• "finding out which groups of columns are often set together and creating indexes for them" is the best approach.

  Also order columns in indices based on 2 criteria:

  • Columns that are more likely to occur when offers take precedence

  • Columns that are more selective take precedence.

    eg. if "where col1 = val1" returns an average of 2 lines for the random value of val1, but "where col2 = val2" returns an average of 2000 lines, col1 must take precedence before the early index

• Not sure about Postgres, but in Sybase, expressions like '%xxx%' (for example, the match value begins with a wildcard) DO NOT get into the index EVER. So an index cannot help with this query unless you select these 3 columns and thus make it a closing index.

Answer 2

Any query filter conditions in which a prefix with a wildcard cannot use an index [suffix is ​​fine, i.e. "abc%")

Answer 3

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

However, you won’t get any benefits from the index if you have wildcards on both sides of the requested text.

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

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