Horton

Re: Streaming read-ready sequential scan code

Thomas Munro <thomas.munro@gmail.com>

View thread View on postgr.es
From: Thomas Munro <thomas.munro@gmail.com>
To: Melanie Plageman <melanieplageman@gmail.com>
Cc: Alexander Lakhin <exclusion@gmail.com>, Andres Freund <andres@anarazel.de>, David Rowley <dgrowleyml@gmail.com>, Heikki Linnakangas <hlinnaka@iki.fi>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2024-08-27T06:52:44Z
Lists: pgsql-hackers

Attachments

Here's a really simple way to see the new unfairness at the end of a
parallel scan:

drop table if exists t;
create table t (i int);
insert into t select generate_series(1, 100000);
alter table t set (parallel_workers = 2);
set parallel_setup_cost = 0;
set parallel_leader_participation = off;
explain (analyze, buffers, verbose) select count(*) from t;

On my machine, unpatched master shows:

                     Worker 0:  actual time=0.036..12.452 rows=51076 loops=1
                       Buffers: shared hit=226
                     Worker 1:  actual time=0.037..12.003 rows=48924 loops=1
                       Buffers: shared hit=217

The attached patch, which I'd like to push, is effectively what
Alexander tested (blocknums[16] -> blocknums[1]).  There's no point in
using an array of size 1, so I've turned it into a simple variable and
deleted the relevant comments.  My machine shows:

                     Worker 0:  actual time=0.038..12.115 rows=49946 loops=1
                       Buffers: shared hit=221
                     Worker 1:  actual time=0.038..12.109 rows=50054 loops=1
                       Buffers: shared hit=222

That difference may not seem huge, but other pre-existing things are
going pathologically wrong in the reported query that magnify it (see
my earlier analysis).  It's an interesting problem that will require
more study (my earlier analysis missed a detail that I'll write about
separately), but it doesn't seem to be new or have easy fixes, so that
will have to be for later work.

Commits

  1. Fix unfairness in all-cached parallel seq scan.

  2. Fix if/while thinko in read_stream.c edge case.

  3. Increase default vacuum_buffer_usage_limit to 2MB.

  4. Allow BufferAccessStrategy to limit pin count.

  5. Improve read_stream.c's fast path.

  6. Secondary refactor of heap scanning functions

  7. Preliminary refactor of heap scanning functions

  8. Add VACUUM/ANALYZE BUFFER_USAGE_LIMIT option