Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 7EEDB2E2D3E
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Dec 2007 16:12:44 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 23014-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue,  4 Dec 2007 16:12:41 -0400 (AST)
Received: from pih-relay04.plus.net (pih-relay04.plus.net [212.159.14.131])
	by postgresql.org (Postfix) with ESMTP id 3B0AA2E3353
	for <pgsql-performance@postgresql.org>;
	Tue,  4 Dec 2007 16:12:32 -0400 (AST)
Received: from [80.229.150.39] (helo=mansionfamily.plus.com)
	by pih-relay04.plus.net with esmtp (Exim) id 1Ize86-0002cU-LL
	for pgsql-performance@postgresql.org; Tue, 04 Dec 2007 20:12:30 +0000
Received: from [192.168.0.87] ([192.168.0.87]:1463)
	by mansionfamily.plus.com with [XMail 1.22 ESMTP Server]
	id <SC6A0> for <pgsql-performance@postgresql.org> from
	<james@mansionfamily.plus.com>; Tue, 4 Dec 2007 20:17:32 -0000
Message-ID: <4755B51E.5040503@mansionfamily.plus.com>
Date: Tue, 04 Dec 2007 20:14:22 +0000
From: James Mansion <james@mansionfamily.plus.com>
Organization: MsgWare
User-Agent: Thunderbird 2.0.0.0 (Windows/20070326)
MIME-Version: 1.0
To: Mark Mielke <mark@mark.mielke.cc>
CC: matthew@flymine.org,  pgsql-performance@postgresql.org
Subject: Re: RAID arrays and performance
References: <Pine.LNX.4.58.0712041213490.3731@aragorn.flymine.org>
	<87lk8aiqf3.fsf@oxford.xeocode.com>
	<Pine.LNX.4.58.0712041247540.3731@aragorn.flymine.org>
	<475559F4.7060104@mark.mielke.cc>
	<Pine.LNX.4.58.0712041349380.3731@aragorn.flymine.org>
	<4755648C.90905@mark.mielke.cc> <87fxyiik94.fsf@oxford.xeocode.com>
	<Pine.LNX.4.58.0712041522320.3731@aragorn.flymine.org>
	<47557519.6090506@mark.mielke.cc>
	<47557872.1000105@mansionfamily.plus.com>
	<47557B12.5040809@mark.mielke.cc>
In-Reply-To: <47557B12.5040809@mark.mielke.cc>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200712/56
X-Sequence-Number: 27872

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