Re: [PATCHES] Bitmap index scan preread using posix_fadvise (Was: There's random access and then there's random access)

[Luke Lonergan]

Nice!

- Luke


On 1/30/08 9:22 AM, Gregory Stark [EMAIL PROTECTED] wrote:

 
 Here's the WIP patch for doing prereading when doing bitmap index scans.
 
 I was performance testing it as I was developing it here:
 
 http://archives.postgresql.org/pgsql-hackers/2007-12/msg00395.php
 
 Note that this only kicks in for bitmap index scans which are kind of tricky
 to generate. I used the attached function to generate them in the post above.
 
 Also note I wouldn't expect to see much benefit unless you're on a raid array,
 even a small one. But if you are on a raid array then the benefit should be
 immediately obvious or else posix_fadvise just isn't working for you. I would
 be interested in hearing on which OSes it does or doesn't work.
 
 *If* this is the approach we want to take rather than restructure the buffer
 manager to avoid taking two trips by marking the buffer i/o-in-progress and
 saving the pinned buffer in the bitmap heap scan then this is more or less in
 final form. Aside from some autoconf tests and the documentation for the GUC I
 think it's all in there.
 
 


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PATCHES] Bitmap index scan preread using posix_fadvise

[Gregory Stark]

Gregory Stark [EMAIL PROTECTED] writes:

 Aside from some autoconf tests and the documentation for the GUC I
 think it's all in there.

I'm sorry, it seems I accidentally grabbed an old tree to generate this patch.
I'll send along a better more recent version. Argh.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PATCHES] Bitmap index scan preread using posix_fadvise

[Gregory Stark]

Attached is the correct patch, sorry for the confusion.

If anyone's interested in testing it you can do so without the randomarray.c
file by using Pavel Stehule's solution for generating arrays:

postgres=# create table h as (select (random()*100)::integer as r, 
repeat('x',512)::text as t from generate_series(1,100));
SELECT

postgres=# create index hri on h(r);
CREATE INDEX

postgres=# analyze h;
ANALYZE

postgres=# \timing
Timing is on.

postgres=# set preread_pages = 0;   explain analyze select * from h where r = 
any (array(select (1+random()*100)::integer from generate_series(1,100)));
postgres=# set preread_pages = 100; explain analyze select * from h where r = 
any (array(select (1+random()*100)::integer from generate_series(1,100)));
postgres=# set preread_pages = 0;   explain analyze select * from h where r = 
any (array(select (1+random()*100)::integer from generate_series(1,100)));
postgres=# set preread_pages = 100; explain analyze select * from h where r = 
any (array(select (1+random()*100)::integer from generate_series(1,100)));
postgres=# set preread_pages = 0;   explain analyze select * from h where r = 
any (array(select (1+random()*100)::integer from generate_series(1,100)));




bitmap-preread-v8.diff.gz
Description: Binary data

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match