From: Greg Stark <stark@enterprisedb.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Greg Smith <gsmith@gregsmith.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
Cc: Postgres <pgsql-hackers@postgresql.org>
Subject: Re: posix_fadvise v22
Date: Fri, 2 Jan 2009 22:02:32 -0500
Message-ID: <4136ffa0901021902m3d9565e8v6d62a75c4a81511a@mail.gmail.com> (raw)
In-Reply-To: <603c8f070901021742s61dcbaf2r51deff1219196944@mail.gmail.com>
References: <FDDBA24E-FF4D-4654-BA75-692B3BA71B97@enterprisedb.com>
<4136ffa0812110911x4914a951o6ca502879e3dede7@mail.gmail.com>
<87k5a6tmy7.fsf@oxford.xeocode.com>
<603c8f070901011230t1afa6644racb715b975078054@mail.gmail.com>
<10658.1230843315@sss.pgh.pa.us>
<603c8f070901011843p121e40afu35c4a7bdc51c051c@mail.gmail.com>
<Pine.GSO.4.64.0901020335150.16680@westnet.com>
<603c8f070901021436v71f10188g1d747b493c89f2f5@mail.gmail.com>
<4136ffa0901021502w78f0611dx63929ac0fbbdc2f4@mail.gmail.com>
<603c8f070901021742s61dcbaf2r51deff1219196944@mail.gmail.com>
On Fri, Jan 2, 2009 at 8:42 PM, Robert Haas <robertmhaas@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: stark@enterprisedb.com, robertmhaas@gmail.com, gsmith@gregsmith.com, tgl@sss.pgh.pa.us, itagaki.takahiro@oss.ntt.co.jp
Subject: Re: posix_fadvise v22
In-Reply-To: <4136ffa0901021902m3d9565e8v6d62a75c4a81511a@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox