Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 7C06E2E024D for ; Wed, 5 Mar 2008 20:27:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 49522-09 for ; Wed, 5 Mar 2008 20:27:08 -0400 (AST) 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 25D172E0210 for ; Wed, 5 Mar 2008 20:27:15 -0400 (AST) Received: from [192.168.1.14] (dcdsl.ebox.com [64.7.143.116]) by beaver.fastcrypt.com (Postfix) with ESMTP id 2AD94158036C for ; Wed, 5 Mar 2008 19:32:11 -0500 (EST) Message-Id: 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: Why the difference in plans ? Date: Wed, 5 Mar 2008 19:27:13 -0500 X-Mailer: Apple Mail (2.919.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/81 X-Sequence-Number: 29035 Below I have two almost identical queries. Strangely enough the one that uses the index is slower ??? explain analyze select uid from user_profile where lower(firstname)='angie' and extract(year from age('2008-02-26 02:50:31.382', dob)) >= 18 and extract(year from age('2008-02-26 02:50:31.382', dob)) <= 68 and image1 is not null and profileprivacy=1 and isactive='t' order by name asc limit 250; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=166423.90..166423.93 rows=11 width=17) (actual time=1033.634..1034.137 rows=129 loops=1) -> Sort (cost=166423.90..166423.93 rows=11 width=17) (actual time=1033.631..1033.811 rows=129 loops=1) Sort Key: name -> Seq Scan on user_profile (cost=0.00..166423.71 rows=11 width=17) (actual time=46.730..1032.994 rows=129 loops=1) Filter: ((lower((firstname)::text) = 'angie'::text) AND (date_part('year'::text, age('2008-02-26 02:50:31.382'::timestamp without time zone, dob)) >= 18::double precision) AND (date_part('year'::text, age('2008-02-26 02:50:31.382'::timestamp without time zone, dob)) <= 68::double precision) AND (image1 IS NOT NULL) AND (profileprivacy = 1) AND isactive) Total runtime: 1034.334 ms (6 rows) jnj=# explain analyze select uid from user_profile where lower(firstname)='angie' and dob <= '1990-03-05 15:17:29.537' and dob >= '1940-03-05 15:17:29.537' and image1 is not null and profileprivacy=1 and isactive='t' order by name asc limit 250; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..113963.92 rows=250 width=17) (actual time=230.326..4688.607 rows=129 loops=1) -> Index Scan using user_profile_name_key on user_profile (cost=0.00..460414.23 rows=1010 width=17) (actual time=230.322..4688.174 rows=129 loops=1) Filter: ((lower((firstname)::text) = 'angie'::text) AND (dob <= '1990-03-05 15:17:29.537'::timestamp without time zone) AND (dob >= '1940-03-05 15:17:29.537'::timestamp without time zone) AND (image1 IS NOT NULL) AND (profileprivacy = 1) AND isactive) Total runtime: 4688.906 ms (4 rows)