From: Jim Nasby <jnasby@enova.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Daniel Farina <daniel@heroku.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Horrific time for getting 1 record from an index?
Date: Mon, 11 Nov 2013 17:28:37 -0600
Message-ID: <52816825.3020605@enova.com> (raw)
In-Reply-To: <CAMkU=1xB9YBFrx2Pr7CyrRC2_PpnC_9jdHRgKRo9upeQo2zfww@mail.gmail.com>
References: <52815099.4080409@enova.com>
<CAAZKuFaroDOtTj1MiZYmv9ZuHck_LQ-B2LjP_gDotqRPXtofMg@mail.gmail.com>
<528152DB.60907@enova.com>
<CAMkU=1xB9YBFrx2Pr7CyrRC2_PpnC_9jdHRgKRo9upeQo2zfww@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jnasby@enova.com, jeff.janes@gmail.com, daniel@heroku.com
Subject: Re: Horrific time for getting 1 record from an index?
In-Reply-To: <52816825.3020605@enova.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox