From: Stephen Frost <sfrost@snowman.net>
To: David Jarvis <thangalin@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
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 17:01:06 -0400
Message-ID: <20100520210106.GO21875@tamriel.snowman.net> (raw)
In-Reply-To: <AANLkTikK_xUrI7iOU9CzpYAKbtxZz-chc3SaHGhTEGXP@mail.gmail.com>
References: <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>
<AANLkTikK_xUrI7iOU9CzpYAKbtxZz-chc3SaHGhTEGXP@mail.gmail.com>
* David Jarvis (thangalin@gmail.com) wrote:
> 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.
[...]
> The greatest() expression reduces to either the current year (year + 0) or
> the next year (year + 1) by taking the sign of the difference in start/end
> days. This allows me to derive an end date, such as:
>
> Dec 22, 1900 to Mar 22, 1901
Something in here really smells fishy to me. Those extract's above are
working on values which are from the table.. Why aren't you using these
functions to figure out how to construct the actual dates based on the
values provided by the *user*..?
Looking at your screenshot, I think you need to take those two date
values that the user provides, make them into actual dates (maybe you
need a CASE statement or something similar, that shouldn't be that hard,
and PG should just run that whole bit once, since to PG's point of view,
it's all constants), and then use those dates to query the tables.
Also, you're trying to do constraint_exclusion, but have you made sure
that it's turned on? And have you made sure that those constraints are
really the right ones and that they make sense? You're using a bunch of
extract()'s there too, why not just specify a CHECK constraint on the
date ranges which are allowed in the table..?
Maybe I've misunderstood the whole point here, but I don't think so.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 196B) download
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: sfrost@snowman.net, thangalin@gmail.com, tgl@sss.pgh.pa.us, thombrown@gmail.com
Subject: Re: Optimize date query for large child tables: GiST or GIN?
In-Reply-To: <20100520210106.GO21875@tamriel.snowman.net>
* 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