Re: Synchronized scans versus relcache reinitialization

On Sat, 2012-05-26 at 15:14 -0400, Tom Lane wrote:
> 3. Having now spent a good deal of time poking at this, I think that the
> syncscan logic is in need of more tuning, and I am wondering whether we
> should even have it turned on by default. It appears to be totally
> useless for fully-cached-in-RAM scenarios, even if most of the relation
> is out in kernel buffers rather than in shared buffers. The best case
> I saw was less than 2X speedup compared to N-times-the-single-client
> case, and that wasn't very reproducible, and it didn't happen at all
> unless I hacked BAS_BULKREAD mode to use a ring buffer size many times
> larger than the current 256K setting (otherwise the timing requirements
> are too tight for multiple backends to stay in sync --- a seqscan can
> blow through that much data in a fraction of a millisecond these days,
> if it's reading from kernel buffers). The current tuning may be all
> right for cases where you're actually reading from spinning rust, but
> that seems to be a decreasing fraction of real-world use cases.

Do you mean that the best case you saw ever was 2X, or the best case
when the table is mostly in kernel buffers was 2X?

I clearly saw better than 2X when the table was on disk, so if you
aren't, we should investigate.

One thing we could do is drive the threshold from effective_cache_size
rather than shared_buffers, which was discussed during 8.3 development.

Regards,
Jeff Davis