On Mon, Jan 23, 2006 at 08:00:01PM +0100, Kovcs Pter wrote: > Are indexes on VARCHAR columns used with the LIKE operator, and if so, > how efficiently are they used? > > I can imagine that using indexes can be easy with the starting literal > characters up to the first percent sign such as in: > > LIKE 'ZOE%QQWE%' > > But, after the first % sign, things can get more difficult. The planner can use an index on the starting literal characters; how "difficult" the query becomes after that depends on how discriminating those initial characters are. If values matching the initial characters comprise a small fraction of the table then the query will probably use an index and be fast, but if they comprise a large fraction of the table, or if the search string starts with a wildcard, then you'll get a sequential scan, which might be slow. > The reason I am asking is that we are thinking about discriminating > between rows of a table based on a VARCHAR column containing various > one-character "flags". We could then use the LIKE operator for > formulating filter conditions. Have you considered putting each flag in a separate column and indexing those columns? If you're using 8.1 the planner would probably use bitmap index scans and come up with a fast plan regardless of which columns you restrict on. And performance issues aside, some people would consider that a better design. However, a disadvantage might be that your queries would be more complex. -- Michael Fuhr
