Re: slow query

Thanks everybody for your help.
VACUUM FULL did the job, and now the query performance is the same in
both databases. I am surprised that FULL option makes such a dramatic
change to the query performance: from 4min. to 5sec.!!! It also changed
planner stats from ~9 sec to ~8sec.
I haven't tried to REINDEX yet, though.
Regarding IN vs. EXISTS. The sub-query in the IN clause will always
return fewer records than 12. I tried using EXISTS instead of IN with
Postgres7.2.1 and it slowed down query performance. With postgres 7.3,
when I use EXISTS instead of IN the planner returns the same stats and
query performance does not improve. However, if I use
m.mediatype=(SELECT objectid FROM mediatype WHERE medianame='Audio')
the planner returns ~7 sec., which is the same as if I the query is
changed like this:
SELECT * FROM media m, speccharacter c, mediatype mt
WHERE mt.objectid=m.mediatype and mt.medianame='Audio'
...
So, using JOIN and =(SELECT ...) is better than using IN and EXISTS in
this case.
Thanks.

Oleg

-----Original Message-----
From: Josh Berkus [mailto:josh(at)agliodbs(dot)com]
Sent: Sunday, February 23, 2003 1:53 PM
To: Oleg Lebedev; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] slow query
Importance: Low

Oleg,

> I VACUUM ANALYZED both databases and made sure they have same indexes
> on the tables.

Have you VACUUM FULL the main database? And how about REINDEX?

> Here is the query:
> SELECT * FROM media m, speccharacter c
> WHERE m.mediatype IN (SELECT objectid FROM mediatype WHERE
> medianame='Audio')

The above should use an EXISTS clause, not IN, unless you are absolutely
sure
that the subquery will never return more than 12 rows.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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

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.

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