Re: Optimize date query for large child tables: GiST or GIN?

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