From: Dave Cramer <pg@fastcrypt.com>
To: postgresql performance list <pgsql-performance@postgresql.org>
Subject: using like in a prepare doesnt' use the right index
Date: Thu, 27 Mar 2008 15:14:49 -0400
Message-ID: <66BB263A-A7B3-4AA1-A350-96A3C44FA395@fastcrypt.com> (raw)
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
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: pg@fastcrypt.com
Subject: Re: using like in a prepare doesnt' use the right index
In-Reply-To: <66BB263A-A7B3-4AA1-A350-96A3C44FA395@fastcrypt.com>
* 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