X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 73110474E4F for ; Sun, 23 Feb 2003 16:05:55 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 24315-01 for ; Sun, 23 Feb 2003 16:05:44 -0500 (EST) Received: from beavis.ybsoft.com (unknown [209.161.7.161]) by postgresql.org (Postfix) with ESMTP id AFE46474E42 for ; Sun, 23 Feb 2003 16:05:44 -0500 (EST) Received: from ns1.ybsoft.com (beavis.ybsoft.com [10.0.0.2]) by beavis.ybsoft.com (Postfix) with ESMTP id 1B3C72B10D; Sun, 23 Feb 2003 14:05:35 -0700 (MST) Subject: Re: slow query From: Ryan Bradetich To: Josh Berkus Cc: Oleg Lebedev , "pgsql-performance@postgresql.org" In-Reply-To: <200302231252.30681.josh@agliodbs.com> References: <993DBE5B4D02194382EC8DF8554A5273033583@postoffice.waterford.org> <200302231252.30681.josh@agliodbs.com> Content-Type: text/plain Organization: Message-Id: <1046034334.13010.3.camel@beavis.ybsoft.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 Date: 23 Feb 2003 14:05:34 -0700 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/167 X-Sequence-Number: 1223 On Sun, 2003-02-23 at 13:52, Josh Berkus wrote: > 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. I am assuming you said this because EXISTS is faster for > 12 rows? Interesting :) thanks, - Ryan -- Ryan Bradetich