"Jeff Amiel" <JAmiel@istreamimaging.com> writes:
> select sum(amount), dates.date as date
> from transaction t
> join (select get_dates as date from
> get_dates('09/17/08','09/24/08')) dates on
> (t.state='I' or t.date1 >= dates.date) and t.date2
> < dates.date
> group by dates.date
The problem you've got here is that the planner has got absolutely no
visibility into the behavior of get_dates(). In particular it doesn't
realize that the values being generated are close to the end of the
range of dates that are in the table, and thus the date1 >= dates.date
condition is far more selective than the date2 < dates.date condition.
If you look closely at the rowcount estimates you'll see that those are
actually being estimated the same, to within roundoff error. So looking
at two indexes instead of one doesn't look like a win to it.
So far as I can see the only way to improve this is to break it up
into separate queries, one for each date, with the date being specified
explicitly in the query.
regards, tom lane