Re: suggestion to improve planer

On Wednesday 09 September 2009 14:11:41 Peter Eisentraut wrote:
> On Thu, 2009-09-03 at 10:35 +0200, Ľubomír Varga wrote:
> > Hi.
> >
> > I hope, that this is right mailing list.
> >
> > SELECT date, value FROM t_event
> > WHERE t_event.id in (SELECT id FROM t_event
> > WHERE date < '2009-08-25'
> > ORDER BY date DESC LIMIT 1)
> > ORDER BY date;
> > cost 6.4
> >
> > SELECT date, value FROM t_event
> > WHERE t_event.id = (SELECT id FROM t_event
> > WHERE date < '2009-08-25'
> > ORDER BY date DESC LIMIT 1)
> > ORDER BY date;
> > cost 6.36..6.37
> >
> >
> > Why that two query dont have equal cost? If it is not problem, try add
> > some planer code to recognize that sublesect HAVE TO return just one row
> > (limit 1) and in plan could be used filter/index scan instead of hash
> > aggregate.
>
> Well, there is always a tradeoff between more planner analysis and more
> complicated and slow planning. Seeing that the cost estimates are close
> enough for practical purposes, it doesn't seem worthwhile to fix
> anything here.
>
> > I have
> > also some complex query examples where cost difference is more visible.
>
> Having real examples where a change might actually improve runtime is
> always more interesting than an academic exercise like the above.

Oka, real world example is attached as txt file. There are 3x2 queries and its
costs on my system/database. System/database is also real, and production.
First set for short time interval and second for long time interval.

Main poblem is, that I want to generate some statistical report and I dont
know for how many "devices" Iam going to generate it for. So if I make some
program, there will be something like:
..."t_device.imei in (" + this.getDevicesImeis() + ")"...
If i have only one device, there could be "=" instead of "in".

PS: Iam very confused about actual costs. I dont have exact query on which I
have seen that "in" for one device in array, is much worse than "=" for one
device. Actual costs are somehow different and "=" select gives about ten
time worse cost.

So probably just ignore my mail and keep planner as is.
My version of postgre: "PostgreSQL 8.2.6 on x86_64-pc-linux-gnu, compiled by
GCC x86_64-pc-linux-gnu-gcc (GCC) 4.1.1 (Gentoo 4.1.1-r3)"

--
Odborník na všetko je zlý odborník. Ja sa snažím byť výnimkou potvrdzujúcou
pravidlo.