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: 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 (view raw or flat)
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

pgsql-performance by date

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

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