Re: [PATCHES] Proposed patch: synchronized_scanning GUC variable

On Wed, 2008-01-30 at 18:42 +0000, Heikki Linnakangas wrote:
> Tom Lane wrote:
> > Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> >> I'm still not very happy with any of the options here.
> >
> >> BAS is great if you didn't want to trash the cache, but its also
> >> annoying to people that really did want to load a large table into
> >> cache. However we set it, we're going to have problems because not
> >> everybody has the same database.
> >
> > That argument leads immediately to the conclusion that you need
> > per-table control over the behavior.
>
> It's even worse than that. Elsewhere in this thread Simon mentioned a
> partitioned table, where each partition on its own is smaller than the
> threshold, but you're seq scanning several partitions and the total size
> of the seq scans is larger than memory size. In that scenario, you would
> want BAS and synchronized scans, but even a per-table setting wouldn't
> cut it.

> For synchronized scans to help in the partitioned situation, I guess
> you'd want to synchronize across partitions. If someone is already
> scanning partition 5, you'd want to start from that partition and join
> the pack, instead of starting from partition 1.

You're right, but in practice its not quite that bad with the
multi-table route. When you have partitions you generally exclude most
of them, with typically 1-2 per query, usually different ones.

If you were scanning lots of partitions in sequence so frequently that
you'd get benefit from synch scans then your partitioning scheme isn't
working for you - and that is the worst problem by far.

But yes, it does need to be addressed.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com