Re: Streaming read-ready sequential scan code

On Mon, Jan 29, 2024 at 4:24 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Tue, 30 Jan 2024 at 10:17, Melanie Plageman
> <melanieplageman(at)gmail(dot)com> wrote:
> > Though logically the performance with 0001 and 0002 should be the same
> > as master (no new non-inline function calls, no additional looping),
> > I've done a bit of profiling anyway. I created a large multi-GB table,
> > read it all into shared buffers (disabling the large sequential scan
> > bulkread optimization), and did a sequential SELECT count(*) from the
> > table. From the profiles below, you'll notice that master and the
> > patch are basically the same. Actual percentages vary from run-to-run.
> > Execution time is the same.
>
> Can you also run a test on a Seqscan with a filter that filters out
> all tuples? There's less overhead in other parts of the executor with
> such a query.

Yes, of course. Thank you so much for taking a look!

While I was at it, I changed the table schema to be entirely composed
of INT type columns and regenerated the data. Note that, both in this
example and my previous example, I ensured that the table was vacuumed
beforehand (and autovacuum disabled for the table) so there wasn't any
on-access pruning happening (heapgetpage() does that in pagemode).

This is the schema
CREATE TABLE foo(id INT, a INT, b INT, c INT, d INT, e INT, f INT, g
INT) with (autovacuum_enabled=false);

I added 46000000 rows to the table, making it 2.6 GB. Shared buffers
is double that. Before profiling, I did a SELECT * from the table with
the large sequential scan bulkread optimization disabled. Then I
vacuumed the table. Finally, I turned up parallel_setup_cost high
enough to disable query parallelism.

The query I profiled was:
SELECT * FROM foo WHERE id = 0;
With the data I generated, 0 rows match that condition.

Profiles below. Execution time essentially the same.

patch:
17.08% postgres postgres [.] ExecInterpExpr
11.17% postgres postgres [.] tts_buffer_heap_getsomeattrs
10.64% postgres postgres [.] ExecStoreBufferHeapTuple
9.82% postgres postgres [.] heap_getnextslot
9.13% postgres postgres [.] heapgettup_pagemode
8.98% postgres postgres [.] heapbuildvis
5.40% postgres postgres [.] HeapCheckForSerializableConflictOut
5.16% postgres postgres [.] SeqNext

master:
17.89% postgres postgres [.] ExecInterpExpr
12.28% postgres postgres [.] tts_buffer_heap_getsomeattrs
10.54% postgres postgres [.] ExecStoreBufferHeapTuple
10.11% postgres postgres [.] heapgettup_pagemode
8.52% postgres postgres [.] heapgetpage
8.28% postgres postgres [.] heap_getnextslot
5.00% postgres postgres [.] HeapCheckForSerializableConflictOut
4.71% postgres postgres [.] SeqNext

- Melanie