Re: The planner hates me. - Mailing list pgsql-general

From Tom Lane
Subject Re: The planner hates me.
Date
Msg-id 7321.1222357121@sss.pgh.pa.us
Whole thread Raw
In response to The planner hates me.  ("Jeff Amiel" <JAmiel@istreamimaging.com>)
Responses Re: The planner hates me.  ("Scott Marlowe" <scott.marlowe@gmail.com>)
List pgsql-general
"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

pgsql-general by date:

Previous
From: Justin Yao
Date:
Subject: Re: how can I find out the numeric directory name of each database in PostgreSQL 8.3
Next
From: "Jeff Amiel"
Date:
Subject: Re: The planner hates me.