Re: expanding our usage of POSIX_FADVISE

On Wed, Aug 12, 2009 at 3:07 PM, Cédric
Villemain<cedric(dot)villemain(at)dalibo(dot)com> wrote:
>
> I wonder if POSIX_FADV_RANDOM and POSIX_FADV_SEQUENTIAL are still innacurate
> for postgreSQL ?
>
> I find
> «A related problem is that the smgr uses the same FD to access the same
> relation no matter how many scans are in progress. Think about a complex
> query that is doing both a seqscan and an indexscan on the same relation (a
> self-join could easily do this). You'd really need to change this if you
> want POSIX_FADV_SEQUENTIAL and POSIX_FADV_RANDOM to get set usefully.
> » (tom lane, 2003)

I had a version of the POSIX_FADV_SEQUENTIAL patch going which set the
appropriate mode before every block read (skipping it if it was the
same mode as last set -- just like we handle lseek). I couldn't
measure any consistent improvement on sequential scans though which,
at least on Linux, already saturdate any i/o system I tested. Mileage
on other operating systems or i/o systems may vary of course.

I think the real benefit of this would be avoiding polluting the
filesystem cache with blocks which we have no intention of reading.
That will be a hard benefit to measure though. Especially since just
because we're doing a random i/o doesn't actually mean we won't read
nearby blocks eventually. If we're scanning an index range and the
table is actually mostly clustered then our random i/o won't be so
random after all...

--
greg
http://mit.edu/~gsstark/resume.pdf