Horton

Re: Streaming read-ready sequential scan code

Heikki Linnakangas <hlinnaka@iki.fi>

View thread View on postgr.es
From: Heikki Linnakangas <hlinnaka@iki.fi>
To: Melanie Plageman <melanieplageman@gmail.com>, Pg Hackers <pgsql-hackers@postgresql.org>
Cc: David Rowley <dgrowleyml@gmail.com>, Andres Freund <andres@anarazel.de>, Thomas Munro <thomas.munro@gmail.com>
Date: 2024-04-02T17:10:37Z
Lists: pgsql-hackers 
On 01/04/2024 22:58, Melanie Plageman wrote:
> Attached v7 has version 14 of the streaming read API as well as a few
> small tweaks to comments and code.

I saw benchmarks in this thread to show that there's no regression when 
the data is in cache, but I didn't see any benchmarks demonstrating the 
benefit of this. So I ran this quick test:

-- create table ~1 GB table with only 1 row per page.
CREATE TABLE giga (i int, filler text) with (fillfactor=10);
insert into giga select g, repeat('x', 900) from generate_series(1, 
140000) g;
vacuum freeze giga;

\timing on
select count(*) from giga;

The SELECT takes about 390 ms on 'master', and 230 ms with the patch.

This is pretty much the best case for this patch, real world gains will 
be much smaller. Nevertheless, nice speedup!

-- 
Heikki Linnakangas
Neon (https://neon.tech)

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