How to optimize index for substring of a column ?
For example, having a column postal_code storing a string of 5 characters. If most of my queries filter on the 2 first characters having an index on this column is not useful.
What if I create an index only on the substring:
CREATE INDEX ON index.annonces_parsed (left(postal_code, 2))
Is it a good solution, or is it better to add a new column storing only the substring and having an index on it ?
A query using this index could be:
select *
from index.cities
where left(postal_code, 2) = '83' --- Will it use the index on the substring ?
Thanks so much