Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50768=pgsql-performance-ar@postgresql.org>)
 id 1Vg0uW-00026D-6u
 for pgsql-performance@arkaria.postgresql.org; Mon, 11 Nov 2013 23:28:48 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50768=pgsql-performance-ar@postgresql.org>)
 id 1Vg0uV-0002Hy-It
 for pgsql-performance@arkaria.postgresql.org; Mon, 11 Nov 2013 23:28:47 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jnasby@enova.com>) id 1Vg0uU-0002Hr-BL
 for pgsql-performance@postgresql.org; Mon, 11 Nov 2013 23:28:46 +0000
Received: from exprod5og108.obsmtp.com ([64.18.0.186])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jnasby@enova.com>) id 1Vg0uR-0004HB-3i
 for pgsql-performance@postgresql.org; Mon, 11 Nov 2013 23:28:45 +0000
Received: from mail-ie0-f179.google.com ([209.85.223.179]) (using TLSv1) by
 exprod5ob108.postini.com ([64.18.4.12]) with SMTP
 ID DSNKUoFoKZAGOUkVvT54Zdi11vLV6wmxkNby@postini.com;
 Mon, 11 Nov 2013 15:28:42 PST
Received: by mail-ie0-f179.google.com with SMTP id u16so5577928iet.10
 for <pgsql-performance@postgresql.org>; Mon, 11 Nov 2013 15:28:40 -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=tvfkDRHiCCAsKAGdQsuU/CQL+soZVgIwG8ePkuWT1Gk=;
 b=BSSi0Ejc1bG3WarxMUVjPPAmZSTOCRQsR88VqiupTaDePdIkSx5gJFZ+Mcc7Ja0qaZ
 LMUp9vATI9ou9oD/wAO8AGf4MGBcfs8IrdtJL334/sMuA4Wh6KeaC2MA3KBuMZdiEtS7
 jYbl5gt+7suj+m/BZPQYXe9Suz1OGtknZ+X98=
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=tvfkDRHiCCAsKAGdQsuU/CQL+soZVgIwG8ePkuWT1Gk=;
 b=G4BLTUb/fXXmmcg7WIGcRbiauR5AptcJW9aX4qkyISP5nvlFDn+Ui4pLcEL7HUkW22
 dgTbqVSqEeRzCJvumAIMEzC778gcj2azgUp2l4HTSyNDJsSRq/ejIeVBtxvoLHCmQ4+b
 braXKP1TQaf8QytnZRG2TBSkxpQMd321BxEWGAexfsDEDpdhie8upDDau44W1dX8kN6J
 WTz0fek3mRwy0k0quUZlcc5lJRzZdsr1rgHr6jEXHmODu37X48nKJOqJWSjTbKJNFAhF
 ThhBbCNa3PnjPIF6PdIRtyj21hQ1h4FxeTzHynRKHhmudcz8ZvHInuJFQljrzcffoNZL
 OpVQ==
X-Gm-Message-State: 
 ALoCoQliusRohMvV/l+TLzVTRR6a+h1IFQ+SpK07dmE/3IgvEZBBNnUSahCBzSVAaP5eq0LpEAEsGdHNOTV02ZMpTbZOc6k3k8jdktxs5Hve65vFXivghSUM3RXuizIxHQ7PFld1FDO97l+bcsT1Ool9XkArpWx0+ax7MnRfS5ZTf/W+pjHeC7LMqEEMHpCxFfhDtwfK0bMm
X-Received: by 10.50.1.102 with SMTP id 6mr14320771igl.0.1384212520858;
 Mon, 11 Nov 2013 15:28:40 -0800 (PST)
X-Received: by 10.50.1.102 with SMTP id 6mr14320766igl.0.1384212520780;
 Mon, 11 Nov 2013 15:28:40 -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 ft2sm21308903igb.5.2013.11.11.15.28.39
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Mon, 11 Nov 2013 15:28:40 -0800 (PST)
Message-ID: <52816825.3020605@enova.com>
Date: Mon, 11 Nov 2013 17:28:37 -0600
From: Jim Nasby <jnasby@enova.com>
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 <jeff.janes@gmail.com>
CC: Daniel Farina <daniel@heroku.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Horrific time for getting 1 record from an index?
References: <52815099.4080409@enova.com>
	<CAAZKuFaroDOtTj1MiZYmv9ZuHck_LQ-B2LjP_gDotqRPXtofMg@mail.gmail.com>
	<528152DB.60907@enova.com>
 <CAMkU=1xB9YBFrx2Pr7CyrRC2_PpnC_9jdHRgKRo9upeQo2zfww@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1xB9YBFrx2Pr7CyrRC2_PpnC_9jdHRgKRo9upeQo2zfww@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -4.3 (----)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 11/11/13 4:57 PM, Jeff Janes wrote:
> On Mon, Nov 11, 2013 at 1:57 PM, Jim Nasby <jnasby@enova.com <mailto:jnasby@enova.com>> wrote:
> Btree indexes have special code that kill index-tuples when the table-tuple is dead-to-all, so only the first such query after the mass deletion becomes vacuum-eligible should be slow, even if a vacuum is not done.  But if there are long running transactions that prevent the dead rows from going out of scope, nothing can be done until those transactions go away.

There is? I didn't know that, can you point me at code?

BTW, I originally had this, even after multiple queries:

            Buffers: shared hit=1 read=9476

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...
-- 
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