Skip site navigation (1) Skip section navigation (2)

Re: posix_fadvise v22

From: "Greg Stark" <stark(at)enterprisedb(dot)com>
To: "Robert Haas" <robertmhaas(at)gmail(dot)com>
Cc: "Greg Smith" <gsmith(at)gregsmith(dot)com>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "ITAGAKI Takahiro" <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>, Postgres <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: posix_fadvise v22
Date: 2009-01-03 03:02:32
Message-ID: 4136ffa0901021902m3d9565e8v6d62a75c4a81511a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2009-01-03 03:20:34
Subject: Re: contrib/pg_stat_statements 1226
Previous:From: Robert HaasDate: 2009-01-03 02:37:31
Subject: Re: Significantly larger toast tables on 8.4?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group