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

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.

>
> 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.
>

Yeah.. In the end, I just made a hack of it and did sort of like a
dymanic sql. (it is a dynamic sql anyway) where I selected the dates
into a variable (the whole query is encapsulated as a function ran at x
intervals) and then use

query_string := replace(query_string,'fromdate',date_inputed_into_variable)
query_string := replace(query_string,'todate',date_inputed_into_variable+refresh_interval)

and that got me much better performance.