To: "Luke Lonergan" <llonergan@greenplum.com>
Cc: "Mark Mielke" <mark@mark.mielke.cc>,
	"Carlo Stonebanks" <stonec.register@sympatico.ca>,
	<pgsql-performance@postgresql.org>
Subject: Re: random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
In-Reply-To: <C30B1716.4236C%llonergan@greenplum.com> (Luke Lonergan's message
	of "Mon\, 10 Sep 2007 15\:45\:26 -0700")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <C30B1716.4236C%llonergan@greenplum.com>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Tue, 11 Sep 2007 08:28:59 +0100
Message-ID: <87lkbdlkxw.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


"Luke Lonergan" <llonergan@greenplum.com> writes:

> Right now the pattern for index scan goes like this:
>
> - Find qualifying TID in index
>   - Seek to TID location in relfile
>     - Acquire tuple from relfile, return
>...
> If we implement AIO and allow for multiple pending I/Os used to prefetch
> groups of qualifying tuples, basically a form of random readahead

Ah, I see what you mean now. It makes a lot more sense if you think of it for
bitmap index scans. So, for example, the bitmap index scan could stream tids
to the executor and the executor would strip out the block numbers and pass
them to the i/o layer saying "i need this block now but following that I'll
need these blocks so get them moving now".

I think this seems pretty impractical for regular (non-bitmap) index probes
though. You might be able to do it sometimes but not very effectively and you
won't know when it would be useful.

I think what this means is that there are actually *three* kinds of i/o: 1)
Sequential which means you get the full bandwidth of your drives * the number
of spindles; 2) Random which gets you 1 block per seek latency regardless of
how many spindles you have; and 3) Random but with prefetch which gets you the
random bandwidth above times the number of spindles.

The extra spindles speed up sequential i/o too so the ratio between sequential
and random with prefetch would still be about 4.0. But the ratio between
sequential and random without prefetch would be even higher.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com