Re: Optimize date query for large child tables: GiST or GIN?

* David Jarvis (thangalin(at)gmail(dot)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