Hey people, long while since I posted here, but I'm having an index
issue that looks on the surface to be a little strange.
I have a text field that I'm trying to query on in a table with
millions of rows. Stupid I know, but a fairly common stupid thing to
try to do.
For some reason it's a requirement that partial wildcard searches are
done on this field, such as "SELECT ... WHERE field LIKE 'A%'"
I thought an interesting way to do this would be to simply create
partial indexes for each letter on that field, and it works when the
query matches the WHERE clause in the index exactly like above. The
problem is thus:
Say I have an index.. CREATE INDEX column_idx_a ON table (column)
WHERE column LIKE 'A%'
It seems to me that a query saying "SELECT column FROM table WHERE
column LIKE 'AA%';" should be just as fast or very close to the first
case up above. However, explain tells me that this query is not using
the index above, which is what's not making sense to me.
Does the planner not realize that 'AA%' will always fall between 'A%'
and 'B', and thus that using the index would be the best way to go, or
am I missing something else that's preventing this from working?