Re: using like in a prepare doesnt' use the right index

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