Why the difference in plans ?
От | Dave Cramer |
---|---|
Тема | Why the difference in plans ? |
Дата | |
Msg-id | E0CF6B25-5191-4AF7-8C72-E67FA2F1AE5E@fastcrypt.com обсуждение исходный текст |
Ответы |
Re: Why the difference in plans ?
(Josh Berkus <josh@agliodbs.com>)
Re: Why the difference in plans ? ("Stephen Denne" <Stephen.Denne@datamail.co.nz>) |
Список | 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)
В списке pgsql-performance по дате отправления:
Следующее
От: "petchimuthu lingam"Дата:
Сообщение: Confirmação de envio / Sending confirmation (captchaid:13266b1124bc)