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

From: David Jarvis <thangalin(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Thom Brown <thombrown(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Date: 2010-05-20 20:09:05
Message-ID: AANLkTimLdBWaVpSF5Sc3ArT_9jcAjhNiB9WBBA4HIyXj@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I was hoping to eliminate this part of the query:

(cast(extract( YEAR FROM m.taken ) + greatest( -1 *
sign(
(extract( YEAR FROM m.taken )||'-12-31')::date -
(extract( YEAR FROM m.taken )||'-01-01')::date ), 0
) AS text)||'-12-31')::date

That uses functions to create the dates, which is definitely the problem.
I'd still have the query return all the results for both data sets. If
providing the query with two data sets won't work, what will?

Dave

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2010-05-20 20:18:58 Re: Optimize date query for large child tables: GiST or GIN?
Previous Message Tom Lane 2010-05-20 20:03:54 Re: Optimize date query for large child tables: GiST or GIN?