From: | David Jarvis <thangalin(at)gmail(dot)com> |
---|---|
To: | Matthew Wakeling <mnw21(at)cam(dot)ac(dot)uk> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Optimize date query for large child tables: GiST or GIN? |
Date: | 2010-05-22 08:11:09 |
Message-ID: | AANLkTilG5hKF_Ue_OWuinYN_2KGUH05qx4j4jAQERh2G@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
CREATE INDEX measurement_01_001_y_idx
>> ON climate.measurement_01_001
>> USING btree
>> (date_part('year'::text, taken));
>>
>> Is that equivalent to what you suggest?
>>
>
> No. It is not the same function, so Postgres has no way to know it produces
> the same results (if it does).
>
This is what I ran:
CREATE INDEX
measurement_013_taken_year_idx
ON
climate.measurement_013
(EXTRACT( YEAR FROM taken ));
This is what pgadmin3 shows me:
CREATE INDEX measurement_013_taken_year_idx
ON climate.measurement_013
USING btree
(date_part('year'::text, taken));
As far as I can tell, it appears they are equivalent?
Either way, the cost for performing a GROUP BY is high (I ran once with
extract and once with date_part). The date_part EXPLAIN ANALYSE resulted in:
"Limit (cost=1748024.65..1748028.65 rows=200 width=12) (actual
time=65471.448..65471.542 rows=101 loops=1)"
The EXTRACT EXPLAIN ANALYSE came to:
"Limit (cost=1748024.65..1748028.65 rows=200 width=12) (actual
time=44913.263..44913.330 rows=101 loops=1)"
If PG treats them differently, I'd like to know how so that I can do the
right thing. As it is, I cannot see the difference in performance between
date_part and EXTRACT.
Dave
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2010-05-22 11:29:30 | Re: pg_dump and pg_restore |
Previous Message | Matthew Wakeling | 2010-05-22 03:26:50 | Re: prepared query performs much worse than regular query |