Postgres Pro
Downloads
Home   >   mailing lists

Re: Optimize date query for large child tables: GiST or GIN? - Mailing list pgsql-performance

From David Jarvis
Subject Re: Optimize date query for large child tables: GiST or GIN?
Date
Msg-id AANLkTilG5hKF_Ue_OWuinYN_2KGUH05qx4j4jAQERh2G@mail.gmail.com
Whole thread Raw
In response to Optimize date query for large child tables: GiST or GIN?  (David Jarvis <thangalin@gmail.com>)
List pgsql-performance
Tree view
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

pgsql-performance by date:

Previous
From: Matthew Wakeling
Date:
Subject: Re: prepared query performs much worse than regular query
Next
From: Robert Haas
Date:
Subject: Re: pg_dump and pg_restore