Re: Streaming read-ready sequential scan code

On Mon, Jan 29, 2024 at 4:24 PM David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Tue, 30 Jan 2024 at 10:17, Melanie Plageman
> <melanieplageman@gmail.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