From: Allen Landsidel <alandsidel@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Strange (?) Index behavior?
Date: Fri, 5 Nov 2004 09:39:16 -0500
Message-ID: <88f1825a041105063955367088@mail.gmail.com> (raw)
Hey people, long while since I posted here, but I'm having an index
issue that looks on the surface to be a little strange.
I have a text field that I'm trying to query on in a table with
millions of rows. Stupid I know, but a fairly common stupid thing to
try to do.
For some reason it's a requirement that partial wildcard searches are
done on this field, such as "SELECT ... WHERE field LIKE 'A%'"
I thought an interesting way to do this would be to simply create
partial indexes for each letter on that field, and it works when the
query matches the WHERE clause in the index exactly like above. The
problem is thus:
Say I have an index.. CREATE INDEX column_idx_a ON table (column)
WHERE column LIKE 'A%'
It seems to me that a query saying "SELECT column FROM table WHERE
column LIKE 'AA%';" should be just as fast or very close to the first
case up above. However, explain tells me that this query is not using
the index above, which is what's not making sense to me.
Does the planner not realize that 'AA%' will always fall between 'A%'
and 'B', and thus that using the index would be the best way to go, or
am I missing something else that's preventing this from working?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alandsidel@gmail.com
Subject: Re: Strange (?) Index behavior?
In-Reply-To: <88f1825a041105063955367088@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox