From: Gavin Sherry <swm@alcove.com.au>
To: Dave Cramer <pg@fastcrypt.com>
Cc: postgresql performance list <pgsql-performance@postgresql.org>
Subject: Re: using like in a prepare doesnt' use the right index
Date: Fri, 28 Mar 2008 15:53:47 +0100
Message-ID: <20080328145347.GE6360@europa.idg.com.au> (raw)
In-Reply-To: <66BB263A-A7B3-4AA1-A350-96A3C44FA395@fastcrypt.com>
References: <66BB263A-A7B3-4AA1-A350-96A3C44FA395@fastcrypt.com>
On Thu, Mar 27, 2008 at 03:14:49PM -0400, Dave Cramer wrote:
> I have a query which is
>
> prepare s_18 as select uid from user_profile where name like
> $1::varchar and isactive=$2 order by name asc limit 250;
>
> explain analyze execute s_18 ('atxchery%','t');
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..7965.22 rows=250 width=14) (actual
> time=301.714..3732.269 rows=1 loops=1)
> -> Index Scan using user_profile_name_key on user_profile
> (cost=0.00..404856.37 rows=12707 width=14) (actual
> time=301.708..3732.259 rows=1 loops=1)
> Filter: (((name)::text ~~ $1) AND (isactive = $2))
> Total runtime: 3732.326 ms
>
> without prepared statements we get
>
> explain analyze select uid from user_profile where name like 'foo%'
> and isactive='t' order by name asc limit 250;
>
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=293.89..294.08 rows=73 width=14) (actual
> time=5.947..6.902 rows=250 loops=1)
> -> Sort (cost=293.89..294.08 rows=73 width=14) (actual
> time=5.943..6.258 rows=250 loops=1)
> Sort Key: name
> Sort Method: top-N heapsort Memory: 38kB
> -> Bitmap Heap Scan on user_profile (cost=5.36..291.64
> rows=73 width=14) (actual time=0.394..2.481 rows=627 loops=1)
> Filter: (isactive AND ((name)::text ~~ 'foo%'::text))
> -> Bitmap Index Scan on user_profile_name_idx
> (cost=0.00..5.34 rows=73 width=0) (actual time=0.307..0.307 rows=628
> loops=1)
> Index Cond: (((name)::text ~>=~ 'foo'::text) AND
> ((name)::text ~<~ 'fop'::text))
>
>
> There are two indexes on it
>
> "user_profile_name_idx" UNIQUE, btree (name varchar_pattern_ops)
> "user_profile_name_key" UNIQUE, btree (name)
>
> one for equality, one for like
This is behaving as designed because the planner transforms the
predicate in the second query: Index Cond: (((name)::text ~>=~
'foo'::text) AND ((name)::text ~<~ 'fop'::text)).
It cannot make this transformation for a prepared statement where the
LIKE argument is a PREPARE parameter (the first query), since the
transformation depends on inspecting the actual string.
You could probably continue using prepared statements and make this
transformation yourself but you'll have to be careful about creating the
'greater' string (see make_greater_string()).
Come to think of it, it'd easier to just make a set returning function
which executes this query, if you need to stick with prepare/execute.
Thanks,
Gavin
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: swm@alcove.com.au, pg@fastcrypt.com
Subject: Re: using like in a prepare doesnt' use the right index
In-Reply-To: <20080328145347.GE6360@europa.idg.com.au>
* 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