Thread: Are indexes used with LIKE?
Hi, 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 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. Any help is appreciated! Regards Peter
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
Thank you for the answer! Sure, the possiblity of having a separate column for each flag was considered, but a common columnn is preferred -- I do not remember exactly why. (I do not directly make that decision.) I guess the main reason is that adding new columns to the table complicates the upgrade procedure with our existing customer base. Thank you again! Peter Michael Fuhr wrote: > 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. >