Re: [PATCHES] Proposed patch: synchronized_scanning GUC variable

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.

One idea would be to look at the access plan and estimate the total size
of all scans in the plan. Another idea would be to switch to a more seq
scan resistant cache replacement algorithm, and forget about the threshold.

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.

I think we'll be wiser after we see some real world use of what we have
there now..

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com