From: | Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Richard Huxton <dev(at)archonet(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Poor Plan selected w/ not provided a date/time but selecting date/time from a table |
Date: | 2007-10-18 01:58:29 |
Message-ID: | 1192672709.27637.13.camel@neuromancer.home.net |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Bret Schuhmacher | 2007-10-18 02:30:26 | Suggestions for Remote Procedure Calls from PG, please? |
Previous Message | Alvaro Herrera | 2007-10-18 01:47:09 | Re: Poor Plan selected w/ not provided a date/time but selecting date/time from a table |