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

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.

I didn't read the entire thread but I've seen similar things go much
better when you grab the constants beforehand and interpolate them into
the query by yourself. Yes, it's really ugly.

--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"Crear es tan difícil como ser libre" (Elsa Triolet)