Re: Slow query

I just ran the query you sent me and attached the output of EXPLAIN
ANALYZE as TOMs_plan.txt
It did not speed up the query significantly.

It always seemed to me that UNION is faster than OR, so I tried your
suggestion to use UNION ALL with the original query without
counter-productive LIMIT 1 in EXISTS clause. This reduced the cost of
the plan by 50%, but slowed down the query. Weird ... The plan is shown
in UNION_ALL_plan.txt

AFAIK, the only change I've done since the time when the query took 3
sec. to run was adding more indexes and increasing the size of data by
about 25%. It sounds kind of stupid, but I remember that adding indexes
sometimes slowed down my queries. I will try to drop all the indexes and
add them back again one by one.

Any other ideas?

Thanks.
Oleg

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, March 24, 2003 1:48 PM
To: Oleg Lebedev
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Slow query

Oleg Lebedev <oleg(dot)lebedev(at)waterford(dot)org> writes:
> SELECT * FROM media m
> WHERE m.mediatype =3D (SELECT objectid FROM mediatype WHERE
> medianame=3D'Audio')=20 AND EXISTS=20
> (SELECT * FROM=20
> (SELECT objectid AS mediaid=20
> FROM media=20
> WHERE activity=3D'347667'=20
> UNION=20
> SELECT ism.media AS mediaid=20
> FROM intsetmedia ism, set s=20
> WHERE ism.set =3D s.objectid=20
> AND s.activity=3D'347667' ) AS a1=20
> WHERE a1.mediaid =3D m.objectid=20
> LIMIT 1)=20
> ORDER BY medianame ASC, status DESC=20

Well, one observation is that the LIMIT clause is useless and probably
counterproductive; EXISTS takes only one row from the subselect anyway.
Another is that the UNION is doing it the hard way; UNION implies doing
a duplicate-elimination step, which you don't need here. UNION ALL
would be a little quicker. But what I would do is split it into two
EXISTS:

SELECT * FROM media m
WHERE m.mediatype = (SELECT objectid FROM mediatype WHERE
medianame='Audio')
AND ( EXISTS(SELECT 1
FROM media
WHERE activity='347667'
AND objectid = m.objectid)
OR EXISTS(SELECT 1
FROM intsetmedia ism, set s
WHERE ism.set = s.objectid
AND s.activity='347667'
AND ism.media = m.objectid))
ORDER BY medianame ASC, status DESC

regards, tom lane

*************************************

This email may contain privileged or confidential material intended for the named recipient only.
If you are not the named recipient, delete this message and all attachments.
Any review, copying, printing, disclosure or other use is prohibited.
We reserve the right to monitor email sent through our network.

*************************************