Re: Optimize date query for large child tables: GiST or GIN? - Mailing list 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