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?
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