From: Yeb Havinga <yebhavinga@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Jarvis <thangalin@gmail.com>
Cc: Thom Brown <thombrown@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Date: Thu, 20 May 2010 23:08:21 +0200
Message-ID: <4BF5A4C5.5090505@gmail.com> (raw)
In-Reply-To: <8132.1274386738@sss.pgh.pa.us>
References: <AANLkTimi_Jh4J6bLZ8lZ0cGQ0VVG7XPCKaFN9GRHd6mP@mail.gmail.com>
<AANLkTimZ9zIEebspkg8Iy8vhEeBhe-JLJeJiYPcW88sJ@mail.gmail.com>
<AANLkTinTEyf_9ooDHv12vfsIuLihB2VqJ6NZGD2DnyHf@mail.gmail.com>
<AANLkTimk7lRS8FHJOot3tF-c6Ds4jexCvWKEQNU1V0Xt@mail.gmail.com>
<7143.1274382139@sss.pgh.pa.us>
<AANLkTim7O3Aalp31WKwBuTufYXeUMQWkwy51nvRcI0QY@mail.gmail.com>
<AANLkTikl8wlHPJaArMWO9XWgG0vk9tununw3eUAYh1IC@mail.gmail.com>
<7767.1274385170@sss.pgh.pa.us>
<AANLkTikOEC-Gr3eqGlyGauRAZJknQEaS5DEWw-ZOcl6r@mail.gmail.com>
<7917.1274385834@sss.pgh.pa.us>
<AANLkTimLdBWaVpSF5Sc3ArT_9jcAjhNiB9WBBA4HIyXj@mail.gmail.com>
<8132.1274386738@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: yebhavinga@gmail.com, tgl@sss.pgh.pa.us, thangalin@gmail.com, thombrown@gmail.com
Subject: Re: Optimize date query for large child tables: GiST or GIN?
In-Reply-To: <4BF5A4C5.5090505@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox