From: | Yeb Havinga <yebhavinga(at)gmail(dot)com> |
---|---|
To: | David Jarvis <thangalin(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Optimize date query for large child tables: GiST or GIN? |
Date: | 2010-05-20 08:20:42 |
Message-ID: | 4BF4F0DA.7060208@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2010-05-20 08:33:15 | Re: Optimize date query for large child tables: GiST or GIN? |
Previous Message | David Jarvis | 2010-05-20 05:06:02 | Optimize date query for large child tables: GiST or GIN? |