Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 01FB6475EDC for ; Mon, 27 Jan 2003 10:17:30 -0500 (EST) Received: from lakemtao02.cox.net (lakemtao02.cox.net [68.1.17.243]) by postgresql.org (Postfix) with ESMTP id 6363A475CA9 for ; Mon, 27 Jan 2003 10:17:29 -0500 (EST) Received: from [192.168.1.1] ([68.11.66.83]) by lakemtao02.cox.net (InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with ESMTP id <20030127151734.JYVJ6744.lakemtao02.cox.net@[192.168.1.1]> for ; Mon, 27 Jan 2003 10:17:34 -0500 Subject: Re: 7.3.1 New install, large queries are slow From: Ron Johnson To: PgSQL Performance ML In-Reply-To: References: <21623.1042865371@sss.pgh.pa.us> <20030127081745.GK15936@perrin.int.nxad.com> Content-Type: text/plain Organization: Message-Id: <1043680648.9896.24.camel@haggis> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 27 Jan 2003 09:17:28 -0600 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/350 X-Sequence-Number: 997 On Mon, 2003-01-27 at 04:34, Curt Sampson wrote: > On Mon, 27 Jan 2003, Sean Chittenden wrote: > > > The FreeBSD VM caching system does prevent one process from exhausting > > another process's cached data due to a sequential access, but the > > FreeBSD VM cache does not try to outsmart sequential data accesses to > > datasets which are larger then available cache space because it's an > > insanely difficult (impossible) problem to solve properly without > > foreknowledge of what data elements will be accessed when. > > This is not impossible; Solaris does just this. I'm a little short of Quite. One way to do it is: - the OS notices that process X has been sequentially reading thru file Y for, say, 3 seconds. - the OS knows that X is currently at the mid-point of file Y - OS says, "Hey, I think I'll be a bit more agressive about, when I have a bit of time, trying to read Y faster than X is requesting it It wouldn't work well, though, in a client-server DB like Postgres, which, in a busy multi-user system, is constantly hitting different parts of different files. The algorithm, though, is used in the RDBMS Rdb. It uses the algorithm above, substituting "process X" for "client X", and passes the agressive reads of Y on to the OS. It's a big win when processing a complete table, like during a CREATE INDEX, or "SELECT foo, COUNT(*)" where there's no index on foo. -- +---------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "Fear the Penguin!!" | +---------------------------------------------------------------+