Re: Seq scans status update - Mailing list pgsql-patches

Tom Lane wrote:
> Heikki Linnakangas <heikki@enterprisedb.com> writes:
>> I've completed a set of performance tests on a test server. The server
>> has 4 GB of RAM, of which 1 GB is used for shared_buffers.
>
> Perhaps I'm misreading it, but these tests seem to show no improvement
> worth spending any effort on --- some of the tests improve a bit but
> many get worse, and that's for tests allegedly designed to highlight the
> improvement; there's been no attempt to measure the distributed cost of
> the additional logic in scenarios where it's not helpful.  To say
> nothing of the likelihood that it will be flat-out counterproductive
> in yet other scenarios.

Yeah, possibly. I've been thinking hard of scenarios where this would be
counterproductive, bulk delete is one that the original patch hurt, but
I think I have all interesting scenarios covered now.

> Should we not just reject the patch and move on to something more
> useful?

If Luke is right, the L2 cache effect that's visible in these in-memory
tests:

  select-medsize-patched            | 00:00:01.332975
  select-medsize-patched            | 00:00:01.33014
  select-medsize-patched            | 00:00:01.332392
  select-medsize-patched            | 00:00:01.333498
  select-medsize-patched            | 00:00:01.332692
  select-medsize-unpatched          | 00:00:02.678069
  select-medsize-unpatched          | 00:00:02.666103
  select-medsize-unpatched          | 00:00:02.673494
  select-medsize-unpatched          | 00:00:02.669645
  select-medsize-unpatched          | 00:00:02.666278

is also visible on larger scans that don't fit in cache with bigger I/O
hardware, and this patch would increase the max. I/O throughput that we
can handle on such hardware. I don't have such hardware available, I
hope someone else will try that.

In addition to that, this should reduce the cache-spoiling effect of big
scans and COPY. That's harder to quantify, I've been planning to run a
TPC-C test with a large SELECT COUNT(*) running in the background to
measure that, but me and the server has been busy with those other tests.

In any case, I do want this for VACUUMs to fix the "WAL flush for every
dirty page" problem. Maybe we should indeed drop the other aspects of
the patch and move on, I'm getting tired of this as well.

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