pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedusing like in a prepare doesnt' use the right index
2+ messages / 2 participants
[nested] [flat]
* using like in a prepare doesnt' use the right index
@ 2008-03-27 19:14 Dave Cramer <pg@fastcrypt.com>
0 siblings, 1 reply; 2+ messages in thread
From: Dave Cramer @ 2008-03-27 19:14 UTC (permalink / raw)
To: pgsql-performance
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
So .... how to get the prepare to use the right index
Dave
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: using like in a prepare doesnt' use the right index
@ 2008-03-28 14:53 Gavin Sherry <swm@alcove.com.au>
parent: Dave Cramer <pg@fastcrypt.com>
0 siblings, 0 replies; 2+ messages in thread
From: Gavin Sherry @ 2008-03-28 14:53 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-03-28 14:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-27 19:14 using like in a prepare doesnt' use the right index Dave Cramer <pg@fastcrypt.com>
2008-03-28 14:53 ` Gavin Sherry <swm@alcove.com.au>
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