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

On Tue, Nov 12, 2013 at 6:22 PM, Jim Nasby <jnasby(at)enova(dot)com> wrote:
> On 11/12/13 6:17 PM, Jeff Janes wrote:
>>
>> 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.
>
>
> Aaaand... that gets to the other problem... our SAN performance is pretty
> abysmal. It took ~270 seconds to read 80MB of index pages (+ whatever heap)
> to get to the first live tuple. (This was run close enough to the vacuum
> that I don't think visibility of these tuples would have changed
> mid-stream).

That's awful, but 'par for the course' for SANs in my experience. If
my math is right, that works out to 27ms / page read. But each index
page read can cause multiple heap page reads depending on how the data
is organized so I think you are up against the laws of physics. All
we can do is to try and organize data so that access patterns are less
radom and/or invest in modern hardware.

merlin