Re: old synchronized scan patch

Jeff,

On 12/4/06 11:07 AM, "Jeff Davis" <pgsql(at)j-davis(dot)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
>
> I could probably clean it up with a little help from someone on this
> list.
>
> Advantages of this patch: If multiple seq scans are going in parallel on
> the same table, it can drastically increase cache hit rate by
> synchronizing the scans. In theory, it should also prevent the problem
> where sequential scans can turn into random access from the disk.
>
> Disadvantages:
> * sequential scans no longer return results in a deterministic order. As
> I understand it, this has effects on the regression tests and possibly a
> few other locations in the code.
> * While the in the use case, it should improve performance quite a lot.
> However, the use case is quite narrow: you need to be running sequential
> scans that are reading tuples from the same table at the same time. That
> means the table can't fit into memory, but must be small enough that it
> is sane to be executing multiple sequential scans on it in parallel.
>
> Is there some interest in this patch?

Yes.

For a scenario of interest let's assume:
1 - the Postgres cache is "scan resistant" such that a seq scan of a large
table is not mapped into the block cache.
2 - the O/S cache is not "scan resistant" so that it will map all blocks
read through the cache
3 - there is I/O prefetch in the OS that keeps the I/O subsystem efficient
under multi-user sequential scanning of different tables/files

With (2), if you issue 5 queries that all seq scan a table that is much
larger than memory, they will complete in nearly the time of one query.
I've tested this up to 1,000 simultaneous queries on Linux and get huge
benefits. This scenario is only sometimes helpful in real production usage.

Given (2), I think sync scan would only have a multi-user benefit when the
scans occur in periods separated by more than the amount of time it takes to
scan a cache size worth of data from disk. For example: If there is 16GB of
RAM, the OS I/O cache might have room for 14GB of blocks. On a good (ahem)
system it might take 14 seconds to scan that amount of data into cache from
disk (yes, 1GB/s). If a new backend begins scanning after that 14 seconds,
it won't benefit from the data in the cache because the new data from disk
replaces the oldest data in cache and so on.

Since we're talking O(seconds) separation between user queries, I think we
could have a benefit.

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. Each query
sequential scans a 10 billion row fact table once, which takes 10 minutes of
the query runtime. With sync scan the last one completes in 35 minutes.
Without sync scan the last completes in 75 minutes. In this case sync scan
significantly improves the experience of 5 people.

> How would I go about proving whether it's useful enough or not?

Can you run the above scenario on a table whose size is ten times the memory
on the machine? As a simple starting point, a simple "SELECT COUNT(*) FROM
BIGTABLE" should be sufficient, but the scans need to be separated by enough
time to invalidate the OS I/O cache.

- Luke