From: Steinar H. Gunderson <sgunderson@bigfoot.com>
To: Moritz Onken <onken@houseofdesign.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Planner should use index on a LIKE 'foo%' query
Date: Sat, 28 Jun 2008 21:19:31 +0200
Message-ID: <20080628191931.GB13013@uio.no> (raw)
In-Reply-To: <287E4428-BECC-46D6-BC47-EA94EA0121F0@houseofdesign.de>
References: <287E4428-BECC-46D6-BC47-EA94EA0121F0@houseofdesign.de>
On Sat, Jun 28, 2008 at 06:24:42PM +0200, Moritz Onken wrote:
> SELECT distinct url from item where url like 'http://www.micro%'; limit
> 10;
Here, the planner knows the pattern beforehand, and can see that it's a
simple prefix.
> select *
> from result
> where exists
> (select * from item where item.url LIKE result.url || '%' limit 1)
> limit 10;
Here it cannot (what if result.url was '%foo%'?).
Try using something like (item.url >= result.url && item.url <= result.url ||
'z'), substituting an appropriately high character for 'z'.
> The only explaination is that I don't use a constant when comparing the
> values. But actually it is a constant...
It's not a constant at planning time.
Also note that you'd usually want to use IN instead of a WHERE EXISTS.
/* Steinar */
--
Homepage: http://www.sesse.net/
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: sgunderson@bigfoot.com, onken@houseofdesign.de
Subject: Re: Planner should use index on a LIKE 'foo%' query
In-Reply-To: <20080628191931.GB13013@uio.no>
* 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