Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id A93879FA0FE for ; Mon, 16 Oct 2006 10:38:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 47652-05 for ; Mon, 16 Oct 2006 13:38:36 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by postgresql.org (Postfix) with ESMTP id D0D6D9F9B05 for ; Mon, 16 Oct 2006 10:38:35 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id 30so726415ugc for ; Mon, 16 Oct 2006 06:38:34 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Q4bsrYungGrjiz1MrzieItRl0HomA7wDNdNmOFnlAnu8NwyeYfrlehvII8+2klz1SuhhIyN47yhMYQ81ouHKxLbpxoF1JOf+w7Hw3eltzLXBlWEgWA0kOw7x0+tvS9d0skx1/bC1lUXCtYV6/LZ8+TuyVN4cO6jtukr8ffI0eks= Received: by 10.78.117.10 with SMTP id p10mr176159huc; Mon, 16 Oct 2006 06:38:33 -0700 (PDT) Received: by 10.78.35.18 with HTTP; Mon, 16 Oct 2006 06:38:33 -0700 (PDT) Message-ID: Date: Mon, 16 Oct 2006 09:38:33 -0400 From: "Merlin Moncure" To: "Carlo Stonebanks" Subject: Re: Performance Optimization for Dummies 2 - the SQL Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <1160153623.6181.13.camel@state.g2switchworks.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.199 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/297 X-Sequence-Number: 21426 On 10/15/06, Carlo Stonebanks wrote: > Hi Merlin, > > Well, I'm back. first of all, thanks for your dogged determination to help > me out - it is much appreciated. I owe you a beer or twelve. > > The import has been running for a week. The import program got faster as I > tuned things. I capture the dynamic SQL statements generated by the app, as > well as an accompanying EXPLAIN - and put it out to an XML file. I turned > off seq scan in the config, and ran a trial import. I knew that with seq > scan off that if I saw a seq scan in my log, it's because there were no > indexes available to satisfy the query - I adjusted accordingly and this > worked really well. > > When the import runs against an empty or small db, it's blisteringly fast > (considering that it's a heauristically based process). This proved that it > wasn't the app or the SQL connection that was slow. Once again, though, as > the data db grows, it slows down. Now it's crawling again. All of the > queries appear to be fine, taking advantage of the indexes. There is ONE > query, though, that seems to be the troublemaker - the same one I had > brought up before. I believe that it is one sub-query that is causing the > problem, taking what appears to be 500 to 1000+ms to run every time. (See > below). > > Curiously, it's using index scans, and it really looks like a simple query > to me. I am completely baffled. The two tables in question have about 800K > rows each - not exactly an incredible number. The EXPLAIN is simple, but the > performance is dreadful. All the other queries run much faster than this - > does ANYTHING about this query strike you as odd? Can you try temporarily disabling bitmap scans and see what comes up? merlin