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

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
hardto 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
throughempty leaf pages is way faster than resolving pages full of pointers to dead-to-all tuple, so the kill code
stillgives 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
indexpages (+ whatever heap) to get to the first live tuple. (This was run close enough to the vacuum that I don't
thinkvisibility of these tuples would have changed mid-stream). 
--
Jim Nasby, Lead Data Architect   (512) 569-9461