X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id A599CD1DA77 for ; Thu, 28 Aug 2003 14:38:44 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 23283-07 for ; Thu, 28 Aug 2003 11:38:16 -0300 (ADT) Received: from ram.rentec.com (ram.rentec.com [192.5.35.66]) by svr1.postgresql.org (Postfix) with ESMTP id 43142D1B8A8 for ; Thu, 28 Aug 2003 11:38:06 -0300 (ADT) Received: from rentec.com (IDENT:618@murre.rentec.com [192.5.35.148]) by ram.rentec.com (8.12.1/8.12.1) with ESMTP id h7SEc7w5000586; Thu, 28 Aug 2003 10:38:08 -0400 (EDT) Message-ID: <3F4E13CF.7080202@rentec.com> Date: Thu, 28 Aug 2003 10:38:07 -0400 From: Michael Guerin User-Agent: Mozilla/5.0 (X11; U; SunOS sun4u; en-US; rv:1.4) Gecko/20030703 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Stephan Szabo Cc: pgsql-performance@postgresql.org Subject: Re: Simple queries take forever to run References: <20030827182051.W85397-100000@megazone.bigpanda.com> In-Reply-To: <20030827182051.W85397-100000@megazone.bigpanda.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/414 X-Sequence-Number: 3230 Stephan Szabo wrote: >On Wed, 27 Aug 2003, Michael Guerin wrote: > > > >>I'm running into some performance problems trying to execute simple >>queries. >> >>postgresql version 7.3.3 >>.conf params changed from defaults. >>shared_buffers = 64000 >>sort_mem = 64000 >>fsync = false >>effective_cache_size = 400000 >> >>ex. query: select * from x where id in (select id from y); >> >>There's an index on each table for id. SQL Server takes <1s to return, >>postgresql doesn't return at all, neither does explain analyze. >> >> > >IN(subquery) is known to run poorly in 7.3.x and earlier. 7.4 is >generally much better (for reasonably sized subqueries) but in earlier >versions you'll probably want to convert into an EXISTS or join form. > > > > Something else seems to be going on, even switching to an exists clause gives much better but poor performance. count(*) where exists clause: Postgresql 19s, SQL Server <1s count(*) where not exists: 23.3s SQL Server 1.5s SQL Server runs on a dual 1.4 with 4gigs, win2k Postgresql runs on a quad 900 with 8 gigs, sunos 5.8