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

[David Jarvis <thangalin@xxxxxxxxx>]

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