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: Alexander Lakhin <exclusion@gmail.com>
Cc: Andres Freund <andres@anarazel.de>, Melanie Plageman <melanieplageman@gmail.com>, David Rowley <dgrowleyml@gmail.com>, Heikki Linnakangas <hlinnaka@iki.fi>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2024-05-18T23:47:52Z
Lists: pgsql-hackers 
On Sun, May 19, 2024 at 7:00 AM Alexander Lakhin <exclusion@gmail.com> wrote:
> With blocknums[1], timing is changed, but the effect is not persistent.
> 10 query15 executions in a row, b7b0f3f27:
> 277.932 ms
> 281.805 ms
> 278.335 ms
> 281.565 ms
> 284.167 ms
> 283.171 ms
> 281.165 ms
> 281.615 ms
> 285.394 ms
> 277.301 ms

The bad time 10/10.

> b7b0f3f27~1:
> 159.789 ms
> 165.407 ms
> 160.893 ms
> 159.343 ms
> 160.936 ms
> 161.577 ms
> 161.637 ms
> 163.421 ms
> 163.143 ms
> 167.109 ms

The good time 10/10.

> b7b0f3f27 + blocknums[1]:
> 164.133 ms
> 280.920 ms
> 160.748 ms
> 163.182 ms
> 161.709 ms
> 161.998 ms
> 161.239 ms
> 276.256 ms
> 161.601 ms
> 160.384 ms

The good time 8/10, the bad time 2/10.

Thanks for checking!  I bet all branches can show that flip/flop
instability in these adverse conditions, depending on random
scheduling details.  I will start a new thread with a patch for the
root cause of that, ie problem #2 (this will need back-patching), and
post a fix for #3 (v17 blocknums[N] tweak affecting
fairness/likelihood, which was probably basically a bit of ill-advised
premature optimisation) here in a few days.

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