Re: Poor Plan selected w/ not provided a date/time but selecting date/time from a table

Ow Mun Heng wrote:
>
> On Wed, 2007-10-17 at 22:47 -0300, Alvaro Herrera wrote:
> > Ow Mun Heng wrote:
> >
> > > Index Cond: ((audit_key_dtime >= $0) AND (audit_key_dtime < $1))
> > >
> > >
> > > Index Cond: ((audit_key_dtime >= '2007-08-08 18:00:00'::timestamp without time zone)
> > >           AND (audit_key_dtime < '2007-08-08 18:01:00'::timestamp without time zone))
> > >
> > > This is _the_ only difference between the 2 queries where on one, the
> > > dates are provided, and the other is selected from a table.
> > >
> > > I have no idea why the plans are so different between the two.
> >
> > The difference is that it has to consider the worst possibility in the
> > second case, whereas the other one knows the interval is just one minute.
>
> Actually the dates are just 1 min apart in both cases.

Of course.  My point is that the planner doesn't know that in the first
case.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.