CODEWITHSUNDEEP
databasepostgresqlindexing
Peter Horn
Peter Horn

Reputation: 13

indexing two varchar columns for query with String concatenation

I have two varchar columns A and B in postgres table with more than 1 000 000 rows. I want to make query with String concatenation SELECT * from table WHERE A||B like 'string%' . How do I make an index that would speed up this query ? I try to make separate indexes or index for both columns, but i does not work for query.

Upvotes: 0

Views: 1830

Answers (1)

user330315
user330315

Reputation:

This should help:

create index on the_table ((a||b) varchar_pattern_ops);

Note the varchar_pattern_ops parameter. This is needed in order to make the index usable for a LIKE query.

Your query must use exactly the same expression ( a||b ) as used in the index, otherwise it will never be used. The condition must be selective enough as well to warrant the index usage. If your query is going to return a substantial percentage of the rows in the table, using the index might not make sense. If your condition only returns a few thousand rows, it might be used by the optimizer.

Don't forget to run analyze after creating the index.

Upvotes: 5

Related Questions