Re: RAID arrays and performance

Mark Mielke wrote:
> At a minimum, this breaks your query into: 1) Preload all the index
> pages you will need
Isn't this fairly predictable - the planner has chosen the index so it
will be operating
on a bounded subset.
> , 2) Scan the index pages you needed
Yes, and AIO helps when you can scan them in arbitrary order, as they
are returned.
> , 3) Preload all the table page you will need
No - just request that they load. You can do work as soon as any page
is returned.
> , 4) Scan the table pages you needed.
In the order that is most naturally returned by the disks.

> But do you really need the whole index?
I don't think I suggested that.
> What if you only need parts of the index, and this plan now reads the
> whole index using async I/O "just in case" it is useful?
Where did you get that from?
> index scan into a regular B-Tree scan, which is difficult to perform
> async I/O for, as you don't necessarily know which pages to read next.
>
Most B-trees are not so deep. It would generally be a win to retain
interior nodes of indices in
shared memory, even if leaf pages are not present. In such a case, it
is quite quick to establish
which leaf pages must be demand loaded.

I'm not suggesting that Postgres indices are structured in a way that
would support this sort
of thing now.

James