From: Yeb Havinga <yebhavinga@gmail.com>
To: David Jarvis <thangalin@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Date: Thu, 20 May 2010 10:20:42 +0200
Message-ID: <4BF4F0DA.7060208@gmail.com> (raw)
In-Reply-To: <AANLkTimi_Jh4J6bLZ8lZ0cGQ0VVG7XPCKaFN9GRHd6mP@mail.gmail.com>
References: <AANLkTimi_Jh4J6bLZ8lZ0cGQ0VVG7XPCKaFN9GRHd6mP@mail.gmail.com>
Hello David,
> The table aggregates 237 million rows from its child tables. The
> sluggishness comes from this part of the query:
>
> 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
Either I had too less coffee and completely misunderstand this
expression, or it is always true and can be omitted. Could you explain a
bit what this part tries to do and maybe also show it's original
counterpart in the source database?
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, thangalin@gmail.com
Subject: Re: Optimize date query for large child tables: GiST or GIN?
In-Reply-To: <4BF4F0DA.7060208@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