Re: There's random access and then there's random access

"Mark Mielke" <mark(at)mark(dot)mielke(dot)cc> writes:

> I didn't see exceeded 3X in the graph. But I certainly see 2X+ for most of the
> graphic, and ~3X for very small reads. Possibly, it is avoiding unnecessary
> read-ahead at the drive or OS levels?

Then you're misreading the graph -- which would be my fault, my picture was
only worth 500 words then.

Ordered scans (simulating a bitmap index scan) is getting 3.8 MB/s a prefetch
of 1 (effectively no prefetch) and 14.1 MB/s with a prefetch of 64. That's a
factor of 3.7!

Unordered scans have an even larger effect (unsurprisingly) going from 1.6MB/s
to 8.9MB/s or a factor of 5.6.

Another surprising bit is that prefetching 8192 blocks, ie, the whole set,
doesn't erase the advantage of the presorting. I would have expected that when
prefetching all the blocks it would make little difference what order we feed
them to posix_fadvise. I guess since the all the blocks which have had i/o
initiated on them haven't been read in yet when we reach the first real read()
that forces some blocks to be read out-of-order. I'm surprised it makes nearly
a 2x speed difference though.

> I think we expected to see raw reads significantly faster for the single
> process case. I thought your simulation was going to involve a tweak to
> PostgreSQL on a real query to see what overall effect it would have on typical
> queries and on special queries like Matthew's. Are you able to tweak the index
> scan and bitmap scan methods to do posfix_fadvise() before running? Even if it
> doesn't do anything more intelligent such as you described in another post?

That's the next step.

I'm debating between two ways to structure the code right now. Do I put the
logic to peek ahead in nodeBitmapHeapScan to read ahead and remember the info
seen or in tidbitmap with an new api function which is only really useful for
this one use case.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!