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)
