Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id CF879633794 for ; Tue, 20 Apr 2010 15:15:31 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 16957-06 for ; Tue, 20 Apr 2010 18:15:21 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pw0-f46.google.com (mail-pw0-f46.google.com [209.85.160.46]) by mail.postgresql.org (Postfix) with ESMTP id 41784632A66 for ; Tue, 20 Apr 2010 15:15:21 -0300 (ADT) Received: by pwj9 with SMTP id 9so4492934pwj.19 for ; Tue, 20 Apr 2010 11:15:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:received:message-id:subject:from:to:cc:content-type; bh=YFF//yDGgq6rurYlGI1HuZdHZNVwsouuo8TyHXWUHh8=; b=FHEE4ssG+HR+nFRe1Meht4gQ1cXU3tziNu4Po7vduBddQWrmejmv+9CGJolr7dTZkp kZ6t64hT5CpK2t7S89hgZYM17fO0stXQrrUShUB7rh4HIL0ivWKQiLgr+I+7iaXJS3BR SerD9tvoRmRqCbdTIRbMrfY3E+hrd6HhAAZjc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=AwOHfXrUgTO9YoFE25qhd/NRLPV3HQ6uMMwQsg/KoqajL/qefceGvRDnuZ0169ebD5 kfx/4KHT6cojGhiwyvi7wDWpMJkSWIM5A4scU9AyGPzKLeEDzdzge60N855T2G+BUQmu ibFD1qLWHQjG8G09PIpOjqRA4civbWh8qqhiE= MIME-Version: 1.0 Received: by 10.231.38.194 with HTTP; Tue, 20 Apr 2010 11:15:19 -0700 (PDT) In-Reply-To: <20100420180351.GA51940@mr-paradox.net> References: <20100420173936.GA50886@mr-paradox.net> <20100420180351.GA51940@mr-paradox.net> Date: Tue, 20 Apr 2010 14:15:19 -0400 Received: by 10.142.59.13 with SMTP id h13mr3053974wfa.168.1271787319987; Tue, 20 Apr 2010 11:15:19 -0700 (PDT) Message-ID: Subject: Re: Very high effective_cache_size == worse performance? From: Robert Haas To: David Kerr Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201004/246 X-Sequence-Number: 38496 On Tue, Apr 20, 2010 at 2:03 PM, David Kerr wrote: > that thought occured to me while I was testing this. I ran a vacuumdb -z > on my database during the load and it didn't impact performance at all. The window to run ANALYZE usefully is pretty short. If you run it before the load is complete, your stats will be wrong. If you run it after the select statements that hit the table are planned, the updated stats won't arrive in time to do any good. > I did turn on log_min_duration_statement but that caused performance to be unbearable, > but i could turn it on again if it would help. I think you need to find a way to identify exactly which query is running slowly. You could sit there and run "select * from pg_stat_activity", or turn on log_min_duration_statement, or have your application print out timestamps at key points, or some other method... ...Robert