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 9AFA09DCB7F for ; Fri, 20 Jan 2006 14:32: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 27579-03 for ; Fri, 20 Jan 2006 14:32:35 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from uproxy.gmail.com (uproxy.gmail.com [66.249.92.194]) by postgresql.org (Postfix) with ESMTP id 78E099DCB7D for ; Fri, 20 Jan 2006 14:32:31 -0400 (AST) Received: by uproxy.gmail.com with SMTP id s2so20927uge for ; Fri, 20 Jan 2006 10:32:31 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:subject:content-type:content-transfer-encoding; b=kwGc4aFAxoKAdWCmZxbz0bU6uWjklHnvYxNGqSRSXbBthdQYKE3hWCMIt8o3bORLh3W8dLTOovbsS2815o9vIIWzn3LocrdtUA6zpjB91B6Vu0c2wOcZWjIUy3w8IxHQDjiZLMTU2eYx/ab/OqwTdTz+DJLmSlgbGf33xCHDYmY= Received: by 10.67.31.14 with SMTP id i14mr940422ugj; Fri, 20 Jan 2006 10:32:31 -0800 (PST) Received: from ?192.168.1.10? ( [81.185.250.153]) by mx.gmail.com with ESMTP id h1sm1364970ugf.2006.01.20.10.32.30; Fri, 20 Jan 2006 10:32:30 -0800 (PST) Message-ID: <43D12CC2.8050408@gmail.com> Date: Fri, 20 Jan 2006 19:32:34 +0100 From: Antoine User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051022) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: query stopped working after tables > 50000 records Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200601/350 X-Sequence-Number: 16828 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.