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
