Jozsef Szalay wrote:
Hi all,
I have to provide a pretty standard query that should return every row
where the NAME attribute begins with a specific string. The type of the
NAME column is varchar. I do have an index for this column. One would
think that Postgres will use the index to look up the matches, but
apparently that is not the case. It performs a full table scan. My
query looks something like this:
SELECT * FROM table WHERE name LIKE ‘smith%’;
Does anyone know a way to “force” the optimizer to utilize the index? Is
there perhaps another way of doing this?
Can you provide an EXPLAIN ANALYZE for the query? This will give us a
hint as to why the index has not been chosen.
The other standard gotcha is that LIKE will not use an index if your
cluster is initialized with locale != C. If it is, then you can try
recreating the index using something like:
CREATE INDEX table_name ON table (name varchar_pattern_ops);
cheers
Mark
