X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 00CC79DC85B for ; Fri, 20 Jan 2006 15:04:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 33570-06 for ; Fri, 20 Jan 2006 15:04:38 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id BFEF39DC842 for ; Fri, 20 Jan 2006 15:04:36 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 532813983D; Fri, 20 Jan 2006 13:04:37 -0600 (CST) Date: Fri, 20 Jan 2006 13:04:37 -0600 From: "Jim C. Nasby" To: Antoine Cc: pgsql-performance@postgresql.org Subject: Re: query stopped working after tables > 50000 records Message-ID: <20060120190437.GN20182@pervasive.com> References: <43D12CC2.8050408@gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <43D12CC2.8050408@gmail.com> X-Operating-System: FreeBSD 6.0-RELEASE amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.098 required=5 tests=[AWL=0.098] X-Spam-Score: 0.098 X-Spam-Level: X-Archive-Number: 200601/355 X-Sequence-Number: 16833 Send query, output of EXPLAIN and table definitions. On Fri, Jan 20, 2006 at 07:32:34PM +0100, Antoine wrote: > Hi, > I have a query that does a left outer join. The query gets some text > from a reference table where one of the query's main tables may or may > not have the text's tables id. It wasn't super fast, but now it simply > won't execute. It won't complete either through odbc or via pgadmin > (haven't yet tried via psql). A week ago (with considerably fewer > records in the main table) it executed fine, not particularly quickly, > but not that slowly either. Now it locks up postgres completely (if > nothing else needs anything it takes 100% cpu), and even after an hour > gives me nothing. I have come up with a solution that gets the text via > another query (possibly even a better solution), but this seems very > strange. > Can anyone shed some light on the subject? I tried a full vacuum on the > tables that needed it, and a postgres restart, all to no avail. > Cheers > Antoine > ps. I can send the query if that will help... > pps. running a home-compiled 8.1.1 with tables in the query having 70000 > records, 30000 records and 10 for the outer join. Without the left outer > join it runs in ~ 1 second. > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461