Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VgOEN-0001ri-4U for pgsql-performance@arkaria.postgresql.org; Wed, 13 Nov 2013 00:22:51 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VgOEM-0001ix-Hq for pgsql-performance@arkaria.postgresql.org; Wed, 13 Nov 2013 00:22:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VgOEL-0001in-ON for pgsql-performance@postgresql.org; Wed, 13 Nov 2013 00:22:49 +0000 Received: from exprod5og109.obsmtp.com ([64.18.0.188]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VgOEI-00065R-9n for pgsql-performance@postgresql.org; Wed, 13 Nov 2013 00:22:49 +0000 Received: from mail-ie0-f175.google.com ([209.85.223.175]) (using TLSv1) by exprod5ob109.postini.com ([64.18.4.12]) with SMTP ID DSNKUoLGT4vxHwhGCo7Avt4nP8QMLLrRZq3z@postini.com; Tue, 12 Nov 2013 16:22:46 PST Received: by mail-ie0-f175.google.com with SMTP id u16so7374383iet.6 for ; Tue, 12 Nov 2013 16:22:39 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enova.com; s=google; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:content-transfer-encoding; bh=EhZ+3pfyuzbagaQriiaIivF0I31Ul06PF/WDx+ry4n0=; b=bLmFqgH1zfkDV6JXuFu08soZrWo7wATUv2O4iuzwbpC4LzxTRdlSt5LoH4/sX4IMkf Lq4e/JGz2snmrH+lcehurUXb+XBJjIUNEFEKNumFCVx27nzRlrGD9m7JbQDLAdoMYyzn 1LMEDyyuVmmDOwIilo8cayEayes11S8jkMV+E= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:message-id:date:from:user-agent:mime-version:to :cc:subject:references:in-reply-to:content-type :content-transfer-encoding; bh=EhZ+3pfyuzbagaQriiaIivF0I31Ul06PF/WDx+ry4n0=; b=MtmWNvS8C3ARgAoDAQAHi3y11HNRmMOSDGaQYWUEyLq/lmjquJetCWmeuY5lujAvHP gGbkNXhcEzL5rrKkTV1yF0zaWlyjgFkXiN7cJ8JmJv95NEiOOy888lRqMFrTgW2oDS6T A6TXoJRaqG1QnCgWce3CdToVp6VbW037yTm8sv3rJxFncSO2NdFJGoCNPUpfOPZekTWJ yIwow6dKIM2yAJ/4mQk8zo2wLGmGTAJjGf8fyP9bv6Ng7rDKKZiM5O3+iUuzX3ioOfjv cV9roXCmFg7ptsoTU1tXGmMNVwfGr8MQP3T2jRpezt81tAdt7tiAGDgknK9Y3xxi0gsV WQsA== X-Gm-Message-State: ALoCoQlmSb7YpJNUXo+RKZKQ1JKlk6+GjJ/N5isN9jnWMbJtiJs5wspx9YRWIP3+eOS/kseovQUjnr2ERRQVOy5y0epzOsfXLnKBy3uHJdwKqkx+jhPrpOH8ROhNiBdjFmzdq2MoxWXSNvpziWXQJ7gBEd6hLDLqxvPEvghouzR4LZCl4OtGlGLOibWym/FDjfEXN+g45hBA X-Received: by 10.50.234.162 with SMTP id uf2mr17106036igc.48.1384302159028; Tue, 12 Nov 2013 16:22:39 -0800 (PST) X-Received: by 10.50.234.162 with SMTP id uf2mr17106032igc.48.1384302158936; Tue, 12 Nov 2013 16:22:38 -0800 (PST) Received: from decina.local (rrcs-67-79-71-90.sw.biz.rr.com. [67.79.71.90]) by mx.google.com with ESMTPSA id q6sm10214igi.0.2013.11.12.16.22.37 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Tue, 12 Nov 2013 16:22:38 -0800 (PST) Message-ID: <5282C64C.5070304@enova.com> Date: Tue, 12 Nov 2013 18:22:36 -0600 From: Jim Nasby User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:24.0) Gecko/20100101 Thunderbird/24.1.0 MIME-Version: 1.0 To: Jeff Janes CC: Daniel Farina , "pgsql-performance@postgresql.org" Subject: Re: Horrific time for getting 1 record from an index? References: <52815099.4080409@enova.com> <528152DB.60907@enova.com> <52816825.3020605@enova.com>

In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -4.3 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On 11/12/13 6:17 PM, Jeff Janes wrote: > BTW, I originally had this, even after multiple queries: > > Buffers: shared hit=1 read=9476 > > > What were the timings like? Upon repeated execution it seems like all the buffers should be loaded and so be "hit", not "read". Well, the problem here is that this is a heavily hit 1.5TB database with 8GB of shared buffers... so stuff has to work hard to stay in buffer (and I didn't run all this immediately one after the other). > Then vacuum: > INFO: index "page_hits_raw_pkey" now contains 50343572 row versions in 182800 pages > DETAIL: 3466871 index row versions were removed. > 44728 index pages have been deleted, 35256 are currently reusable. > > Then... > > Buffers: shared hit=1 read=4 > > So I suspect a vacuum is actually needed... > > > Hmm. Maybe the kill method doesn't unlink the empty pages from the tree? > > > I verified that this is the case--the empty pages remain linked in the tree until a vacuum removes them. But walking through empty leaf pages is way faster than resolving pages full of pointers to dead-to-all tuple, so the kill code still gives a huge benefit. But of course nothing will do much good until the transaction horizon advances. Aaaand... that gets to the other problem... our SAN performance is pretty abysmal. It took ~270 seconds to read 80MB of index pages (+ whatever heap) to get to the first live tuple. (This was run close enough to the vacuum that I don't think visibility of these tuples would have changed mid-stream). -- Jim Nasby, Lead Data Architect (512) 569-9461 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance