On Mon, Nov 11, 2013 at 3:28 PM, Jim Nasby <jnasby@enova.com> wrote:
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?
git grep "kill_prior_tuple"
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".
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.