The var_char_pattern_ops operator group has made the difference. Thanks for the help! Jozsef -----Original Message----- From: Mark Kirkwood [mailto:markir@xxxxxxxxxxxxxxx] Sent: Thursday, March 02, 2006 7:29 PM To: Jozsef Szalay Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: [PERFORM] Like 'name%' is not using index 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
