Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 637132E2EB0 for ; Tue, 4 Dec 2007 12:07:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 10229-02-2 for ; Tue, 4 Dec 2007 12:07:16 -0400 (AST) Received: from ironport2-out.pppoe.ca (ironport2-out.pppoe.ca [206.248.154.182]) by postgresql.org (Postfix) with ESMTP id D083D2E311C for ; Tue, 4 Dec 2007 12:07:20 -0400 (AST) X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: Ao8CALYJVUfO+Igp/2dsb2JhbACRHw X-IronPort-AV: E=Sophos;i="4.23,249,1194238800"; d="scan'208";a="10769341" Received: from smtp.pppoe.ca ([65.39.196.238]) by ironport2-out.pppoe.ca with ESMTP; 04 Dec 2007 11:07:19 -0500 Received: from [206.248.136.41] ([206.248.136.41]) by smtp.pppoe.ca (Internet Mail Server v1.0) with ESMTP id JCL02320; Tue, 04 Dec 2007 11:07:20 -0500 Message-ID: <47557B12.5040809@mark.mielke.cc> Date: Tue, 04 Dec 2007 11:06:42 -0500 From: Mark Mielke User-Agent: Thunderbird 2.0.0.9 (X11/20071115) MIME-Version: 1.0 To: James Mansion CC: matthew@flymine.org, pgsql-performance@postgresql.org Subject: Re: RAID arrays and performance References: <87lk8aiqf3.fsf@oxford.xeocode.com> <475559F4.7060104@mark.mielke.cc> <4755648C.90905@mark.mielke.cc> <87fxyiik94.fsf@oxford.xeocode.com> <47557519.6090506@mark.mielke.cc> <47557872.1000105@mansionfamily.plus.com> In-Reply-To: <47557872.1000105@mansionfamily.plus.com> 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/46 X-Sequence-Number: 27862 James Mansion wrote: > Mark Mielke wrote: >> This assumes that you can know which pages to fetch ahead of time - >> which you do not except for sequential read of a single table. > Why doesn't it help to issue IO ahead-of-time requests when you are > scanning an index? You can read-ahead > in index pages, and submit requests for data pages as soon as it is > clear you'll want them. Doing so can allow > the disks and OS to relax the order in which you receive them, which > may allow you to process them while IO > continues, and it may also optimise away some seeking and settle > time. Maybe. Sorry to be unclear. To achieve a massive speedup (12X for 12 disks with RAID 0) requires that you know what reads to perform in advance. The moment you do not, you only have a starting point, your operations begin to serialize again. For example, you must scan the first index, to be able to know what table rows to read. At a minimum, this breaks your query into: 1) Preload all the index pages you will need, 2) Scan the index pages you needed, 3) Preload all the table page you will need, 4) Scan the table pages you needed. But do you really need the whole index? 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? Index is a B-Tree for a reason. In Matthew's case where he has an IN clause with thousands of possibles (I think?), perhaps a complete index scan is always the best case - but that's only one use case, and in my opinion, an obscure one. As soon as additional table joins become involved, the chance that whole index scans are required would probably normally reduce, which turns the 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. It seems like a valuable goal - but throwing imaginary numbers around does not appeal to me. I am more interested in Gregory's simulations. I would like to understand his simulation better, and see his results. Speculation about amazing potential is barely worth the words used to express it. The real work is in design and implementation. :-) Cheers, mark -- Mark Mielke