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

Tom Lane wrote:
> David Jarvis <thangalin@gmail.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