From: | Dave Cramer <pg(at)fastcrypt(dot)com> |
---|---|
To: | postgresql performance list <pgsql-performance(at)postgresql(dot)org> |
Subject: | Why the difference in plans ? |
Date: | 2008-03-06 00:27:13 |
Message-ID: | E0CF6B25-5191-4AF7-8C72-E67FA2F1AE5E@fastcrypt.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | petchimuthu lingam | 2008-03-06 04:48:48 | Confirmação de envio / Sending confirmation (captchaid:13266b1124bc) |
Previous Message | Bill Moran | 2008-03-05 16:00:13 | Re: postgresql performance |