Re: posix_fadvise v22

On Fri, Jan 2, 2009 at 8:42 PM, Robert Haas <robertmhaas(at)gmail(dot)com> 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