Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 24AB464FD78 for ; Fri, 2 Jan 2009 23:09:47 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 77006-10 for ; Fri, 2 Jan 2009 23:09:42 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f19.google.com (mail-bw0-f19.google.com [209.85.218.19]) by mail.postgresql.org (Postfix) with ESMTP id EB2C964FCF2 for ; Fri, 2 Jan 2009 23:09:39 -0400 (AST) Received: by bwz12 with SMTP id 12so17796561bwz.19 for ; Fri, 02 Jan 2009 19:09:38 -0800 (PST) Received: by 10.223.106.68 with SMTP id w4mr13236562fao.20.1230951752693; Fri, 02 Jan 2009 19:02:32 -0800 (PST) Received: by 10.223.115.79 with HTTP; Fri, 2 Jan 2009 19:02:32 -0800 (PST) Message-ID: <4136ffa0901021902m3d9565e8v6d62a75c4a81511a@mail.gmail.com> Date: Fri, 2 Jan 2009 22:02:32 -0500 From: "Greg Stark" To: "Robert Haas" Subject: Re: posix_fadvise v22 Cc: "Greg Smith" , "Tom Lane" , "ITAGAKI Takahiro" , Postgres In-Reply-To: <603c8f070901021742s61dcbaf2r51deff1219196944@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <4136ffa0812110911x4914a951o6ca502879e3dede7@mail.gmail.com> <87k5a6tmy7.fsf@oxford.xeocode.com> <603c8f070901011230t1afa6644racb715b975078054@mail.gmail.com> <10658.1230843315@sss.pgh.pa.us> <603c8f070901011843p121e40afu35c4a7bdc51c051c@mail.gmail.com> <603c8f070901021436v71f10188g1d747b493c89f2f5@mail.gmail.com> <4136ffa0901021502w78f0611dx63929ac0fbbdc2f4@mail.gmail.com> <603c8f070901021742s61dcbaf2r51deff1219196944@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/98 X-Sequence-Number: 130742 On Fri, Jan 2, 2009 at 8:42 PM, Robert Haas wrote: >> Hm, what were those plans? You might want to put the old code back in >> explain.c to print the prefetching target to see how well it's doing. > > Well, bad news. Here's one where prefetching seems to make it WORSE. > > rhaas=# explain select sum(1) from enormous where l_shipdate in > ('1992-01-01', '1993-01-01', '1994-01-01', '1995-01-01', '1996-01-01', > '1997-01-01', '1998-01-01', '1999-01-01', '2000-01-01', '2001-01-01'); > > QUERY PLAN > > -------------------------------------------------------------------------------------------------------------------------------------------------------------- > ------------ > Aggregate (cost=455072.75..455072.76 rows=1 width=0) > -> Bitmap Heap Scan on enormous (cost=3327.59..454634.09 > rows=175464 width=0) Any chance you could put back the code in explain.c which showed whether posix_fadvise is actually getting used? Another thing I did when testing was attaching with strace to see if posix_fadvise (the syscall on linux was actually fadvise64 iirc) is actually getting called. And is this on a system with multiple spindles? How many? And how much of the data is in shared buffers or in filesystem cache? Is this consistent for repeated queries? Is it only when you're repeating a query for dates that you've already selected? And how fast is it with effective_io_concurrency set to 1,2,3,5,6,7,8,...? Do you see the same effect if you use a self-contained test case instead of the TPC-H data so I can try it? -- greg