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

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

From: Yeb Havinga <yebhavinga(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: David Jarvis <thangalin(at)gmail(dot)com>, 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 21:08:21
Message-ID: 4BF5A4C5.5090505@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> 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?
>   
That somebody was probably me. I still think the whole BETWEEN 
expression is a tautology. A small test did not provide a 
counterexample. In the select below everything but the select was 
copy/pasted.

create table m (taken timestamptz);
insert into m values (now());
insert into m values ('1900-12-31');
insert into m values ('2000-04-06');
select m.taken BETWEEN
        /* Start date. */
      (extract( YEAR FROM m.taken )||'-01-01')::date AND
        /* End date. Calculated by checking to see if the end date wraps
          into the next year. If it does, then add 1 to the current year.
        */
        (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 from m;
  ?column?
----------
 t
 t
 t
(3 rows)

Another thing is that IF the climate measurements is partitioned on time 
(e.g each year?), then a function based index on the year part of 
m.taken is useless, pardon my french. I'm not sure if it is partitioned 
that way but it is an interesting thing to inspect, and perhaps rewrite 
the query to use constraint exclusion.

regards,
Yeb Havinga


In response to

pgsql-performance by date

Next:From: Stephen FrostDate: 2010-05-20 21:19:19
Subject: Re: Optimize date query for large child tables: GiST orGIN?
Previous:From: Stephen FrostDate: 2010-05-20 21:01:06
Subject: Re: Optimize date query for large child tables: GiST orGIN?

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