X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 701ABD1B2E6 for ; Tue, 3 Aug 2004 11:09:14 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 16357-05 for ; Tue, 3 Aug 2004 14:06:27 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 7DB28D1B192 for ; Tue, 3 Aug 2004 11:03:43 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 9D911354F2; Tue, 3 Aug 2004 07:03:31 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 9C28E354F1; Tue, 3 Aug 2004 07:03:31 -0700 (PDT) Date: Tue, 3 Aug 2004 07:03:31 -0700 (PDT) From: Stephan Szabo To: Marius Andreiana Cc: pgsql-performance@postgresql.org Subject: Re: NOT IN query takes forever In-Reply-To: <1091522942.4936.5.camel@marte.biciclete.ro> Message-ID: <20040803065928.Y88907@megazone.bigpanda.com> References: <1091522942.4936.5.camel@marte.biciclete.ro> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.1 tagged_above=0.0 required=5.0 tests=NO_DNS_FOR_FROM X-Spam-Level: * X-Archive-Number: 200408/29 X-Sequence-Number: 7665 On Tue, 3 Aug 2004, Marius Andreiana wrote: > I just vacuum analyse'd the database. > > Trying to run this query: > EXPLAIN ANALYSE > select * FROM trans > WHERE query_id NOT IN (select query_id FROM query) > > but it will remain like that forever (cancelled after 30 min). > > My postgresql.conf is the default: > # - Memory - > > shared_buffers = 1000 # min 16, at least max_connections*2, > 8KB each > #sort_mem = 1024 # min 64, size in KB > #vacuum_mem = 8192 # min 1024, size in KB > > Should I adjust something? Probably sort_mem. It's probably estimating that it can't hash the result into the 1MB of sort_mem so it's probably falling back to some sort of nested execution.