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