Re: old synchronized scan patch

"Luke Lonergan" <llonergan@greenplum.com> writes:
> On 12/4/06 11:07 AM, "Jeff Davis" <pgsql@j-davis.com> wrote:
>> Now that 8.3 is open, I was considering a revival of this old patch:
>> http://archives.postgresql.org/pgsql-hackers/2005-02/msg00832.php

> Where I think sync scan could have a big benefit is for multi-user business
> intelligence workloads where there are a few huge fact tables of interest to
> a wide audience.  Example: 5 business analysts come to work at 9AM and start
> ad-hoc queries expected to run in about 15 minutes each.

The problem I've got with this is that the use-case seems a bit narrow
(maybe not to Greenplum, but compared to the general Postgres audience)
whereas the proposed patch is invasive in terms of changing
well-established behavior, and what's worse, potentially *reducing*
performance for average workloads.  In particular I'm concerned about
the shared-memory area where the scan status is stored becoming a
contention bottleneck.  It would presumably have access demand
comparable to the bufmgr, ie one hit per page scanned, and we already
know that it's extremely hard to keep bufmgr from being a major source
of contention.

One thing you might consider doing is to have the patch do nothing when
scanning tables that are less than, perhaps, 10x shared_buffers long.
This would at least avoid the regression-test-breakage problem (at the
cost of not having the patch tested at all by said tests :-().

Anyway I think the major stumbling block is to be able to show that the
patch has only negligible performance impact in cases where it's not
able to provide a benefit.
        regards, tom lane