DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=GyVGxgFHHNQNSv10VS0yTaijEt32DcazcgPqrRvQhTur+yJt3BaugvdVdk5OPPsF4S
	z+yEi6BZYFrOtgDy5sQ66Mn6xUTVvYzP1T8YhzCWmFAmGB9NNtZEXIXcL5B9qtEyMdDO
	A7ThYSIqgF7zctxfXijgOvXu5+YJw17grChrM=
Message-ID: <603c8f070901021742s61dcbaf2r51deff1219196944@mail.gmail.com>
Date: Fri, 2 Jan 2009 20:42:51 -0500
From: "Robert Haas" <robertmhaas@gmail.com>
To: "Greg Stark" <stark@enterprisedb.com>
Subject: Re: posix_fadvise v22
Cc: "Greg Smith" <gsmith@gregsmith.com>, "Tom Lane" <tgl@sss.pgh.pa.us>,
	"ITAGAKI Takahiro" <itagaki.takahiro@oss.ntt.co.jp>,
	Postgres <pgsql-hackers@postgresql.org>
In-Reply-To: <4136ffa0901021502w78f0611dx63929ac0fbbdc2f4@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <FDDBA24E-FF4D-4654-BA75-692B3BA71B97@enterprisedb.com>
	<8347.1229012970@sss.pgh.pa.us>
	<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>

> 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)
         Recheck Cond: (l_shipdate = ANY
('{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}'::d
ate[]))
         ->  Bitmap Index Scan on enormous_l_shipdate
(cost=0.00..3283.72 rows=175464 width=0)
               Index Cond: (l_shipdate = ANY
('{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}
'::date[]))
(5 rows)

With effective_io_concurrency set to 1, this took 32 s.  With
effective_io_concurrency set to 4, it took 50 s.  The table was
created like this:

create table enormous as select l.*, l_instance from lineitem l,
generate_series(1, 8) l_instance;
create index enormous_l_shipdate on enormous (l_shipdate);
vacuum analyze enormous;

...where lineitem is from the skewed TPC-H data for the histojoin patch.

...Robert