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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: David Jarvis <thangalin(at)gmail(dot)com>
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:18:58
Message-ID: 8132.1274386738@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
David Jarvis <thangalin(at)gmail(dot)com> writes:
> 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.

Well, it's not the functions per se that's the problem, it's the lack of
a useful index on the expression.  But as somebody remarked upthread,
that expression doesn't look correct at all.  Doesn't the whole
greatest() subexpression reduce to a constant?

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: David JarvisDate: 2010-05-20 20:19:25
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Previous:From: David JarvisDate: 2010-05-20 20:09:05
Subject: Re: Optimize date query for large child tables: GiST or GIN?

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