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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
