Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 92D682E0047 for ; Thu, 27 Mar 2008 16:14:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 03932-09 for ; Thu, 27 Mar 2008 16:14:48 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from beaver.fastcrypt.com (beaver.fastcrypt.com [64.7.141.17]) by postgresql.org (Postfix) with ESMTP id 1DEB02E0057 for ; Thu, 27 Mar 2008 16:14:52 -0300 (ADT) Received: from [192.168.1.14] (dcdsl.ebox.com [64.7.143.116]) by beaver.fastcrypt.com (Postfix) with ESMTP id E1C4A158037C for ; Thu, 27 Mar 2008 15:22:12 -0400 (EDT) Message-Id: <66BB263A-A7B3-4AA1-A350-96A3C44FA395@fastcrypt.com> From: Dave Cramer To: postgresql performance list Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Mime-Version: 1.0 (Apple Message framework v919.2) Subject: using like in a prepare doesnt' use the right index Date: Thu, 27 Mar 2008 15:14:49 -0400 X-Mailer: Apple Mail (2.919.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/509 X-Sequence-Number: 29463 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