Re: Horrific time for getting 1 record from an index?

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
queryafter the mass deletion becomes vacuum-eligible should be slow, even if a vacuum is not done.  But if there are
longrunning transactions that prevent the dead rows from going out of scope, nothing can be done until those
transactionsgo 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