Skip site navigation (1) Skip section navigation (2)

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

From: David Jarvis <thangalin(at)gmail(dot)com>
To: Matthew Wakeling <mnw21(at)cam(dot)ac(dot)uk>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Date: 2010-05-22 08:11:09
Message-ID: AANLkTilG5hKF_Ue_OWuinYN_2KGUH05qx4j4jAQERh2G@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Robert HaasDate: 2010-05-22 11:29:30
Subject: Re: pg_dump and pg_restore
Previous:From: Matthew WakelingDate: 2010-05-22 03:26:50
Subject: Re: prepared query performs much worse than regular query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group