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.
explain
SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk = (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
device.imei = (SELECT imei FROM t_device WHERE id = 3 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
date between '2009-08-25' AND '2009-08-27'
UNION
SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk = (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
device.imei = (SELECT imei FROM t_device WHERE id = 3 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
t_event.id = (SELECT id FROM t_event
WHERE date > '2009-08-27' AND
event_type_fk = (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
t_event.device_fk = device.id
ORDER BY date ASC LIMIT 1)
ORDER BY spz_number, date;
cost 2573
explain
SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk in (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
device.imei in (SELECT imei FROM t_device WHERE id = 3 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
date between '2009-08-25' AND '2009-08-27'
UNION
SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk in (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
device.imei in (SELECT imei FROM t_device WHERE id = 3 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
t_event.id = (SELECT id FROM t_event
WHERE date > '2009-08-27' AND
event_type_fk in (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
t_event.device_fk = device.id
ORDER BY date ASC LIMIT 1)
ORDER BY spz_number, date;
cost 2109
explain
SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk = (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
device.imei in (SELECT imei FROM t_device WHERE id = 3 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
date between '2009-08-25' AND '2009-08-27'
UNION
SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk = (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
device.imei in (SELECT imei FROM t_device WHERE id = 3 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
t_event.id = (SELECT id FROM t_event
WHERE date > '2009-08-27' AND
event_type_fk = (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
t_event.device_fk = device.id
ORDER BY date ASC LIMIT 1)
ORDER BY spz_number, date;
cost 2039
---------------------------------------------------------------------------------------
Extendet time interval:
explain
SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk = (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
device.imei = (SELECT imei FROM t_device WHERE id = 3 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
date between '2009-08-25' AND '2009-08-27'
UNION
SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk = (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
device.imei = (SELECT imei FROM t_device WHERE id = 3 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
t_event.id = (SELECT id FROM t_event
WHERE date > '2009-08-27' AND
event_type_fk = (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
t_event.device_fk = device.id
ORDER BY date ASC LIMIT 1)
ORDER BY spz_number, date;
cost 32614
explain
SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk in (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
device.imei in (SELECT imei FROM t_device WHERE id = 3 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
date between '2009-08-25' AND '2009-08-27'
UNION
SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk in (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
device.imei in (SELECT imei FROM t_device WHERE id = 3 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
t_event.id = (SELECT id FROM t_event
WHERE date > '2009-08-27' AND
event_type_fk in (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
t_event.device_fk = device.id
ORDER BY date ASC LIMIT 1)
ORDER BY spz_number, date;
cost 2711
explain
SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk = (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
device.imei in (SELECT imei FROM t_device WHERE id = 34 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
date between '2009-07-25' AND '2009-08-25'
UNION
SELECT spz_number, date, value FROM t_event, t_device_cache, t_device device
WHERE event_type_fk = (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
device.imei in (SELECT imei FROM t_device WHERE id = 34 limit 1) AND
device.id = t_event.device_fk AND
t_device_cache.imei = device.imei AND
t_event.id = (SELECT id FROM t_event
WHERE date > '2009-08-25' AND
event_type_fk = (SELECT id FROM t_event_type WHERE type = 10
LIMIT 1) AND
t_event.device_fk = device.id
ORDER BY date ASC LIMIT 1)
ORDER BY spz_number, date;
cost 2732
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
