From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Kovács Péter <peter(dot)kovacs(at)chemaxon(dot)hu> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Are indexes used with LIKE? |
Date: | 2006-01-24 22:40:25 |
Message-ID: | 20060124224025.GA91903@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Carl Conard | 2006-01-24 23:11:23 | Re: Installing Postgres 8.1 on Windows Server 2003 R2 |
Previous Message | Michael Fuhr | 2006-01-24 22:09:00 | Re: FATAL: invalid frontend message type 47 |