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

Re: posix_fadvise v22

From: "Robert Haas" <robertmhaas(at)gmail(dot)com>
To: "Greg Stark" <stark(at)enterprisedb(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 01:42:51
Message-ID: 603c8f070901021742s61dcbaf2r51deff1219196944@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
> 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

In response to

Responses

pgsql-hackers by date

Next:From: Joe ConwayDate: 2009-01-03 01:43:42
Subject: Re: BUG #4599: bugfix for contrib/dblink module
Previous:From: Alex HunsakerDate: 2009-01-03 01:37:28
Subject: Re: new libpq SSL connection option

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