Horton

Re: BitmapHeapScan streaming read user and prelim refactoring

Tomas Vondra <tomas.vondra@enterprisedb.com>

View thread View on postgr.es
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Melanie Plageman <melanieplageman@gmail.com>
Cc: Andres Freund <andres@anarazel.de>, Pg Hackers <pgsql-hackers@postgresql.org>, Thomas Munro <thomas.munro@gmail.com>, Heikki Linnakangas <hlinnaka@iki.fi>, Nazir Bilal Yavuz <byavuz81@gmail.com>
Date: 2024-02-28T14:56:06Z
Lists: pgsql-hackers 
On 2/28/24 15:38, Melanie Plageman wrote:
> On Wed, Feb 28, 2024 at 8:22 AM Tomas Vondra
> <tomas.vondra@enterprisedb.com> wrote:
>>
>> Hi,
>>
>> I haven't looked at the code very closely yet, but I decided to do some
>> basic benchmarks to see if/how this refactoring affects behavior.
>>
>> Attached is a simple .sh script that
>>
>> 1) creates a table with one of a couple basic data distributions
>> (uniform, linear, ...), with an index on top
>>
>> 2) runs a simple query with a where condition matching a known fraction
>> of the table (0 - 100%), and measures duration
>>
>> 3) the query is forced to use bitmapscan by disabling other options
>>
>> 4) there's a couple parameters the script varies (work_mem, parallel
>> workers, ...), the script drops caches etc.
>>
>> 5) I only have results for table with 1M rows, which is ~320MB, so not
>> huge. I'm running this for larger data set, but that will take time.
>>
>>
>> I did this on my two "usual" machines - i5 and xeon. Both have flash
>> storage, although i5 is SATA and xeon has NVMe. I won't share the raw
>> results, because the CSV is like 5MB - ping me off-list if you need the
>> file, ofc.
> 
> I haven't looked at your results in detail yet. I plan to dig into
> this more later today. But, I was wondering if it was easy for you to
> run the shorter tests on just the commits before the last
> https://github.com/melanieplageman/postgres/tree/bhs_pgsr
> i.e. patches 0001-0013. Patch 0014 implements the streaming read user
> and removes all of the existing prefetch code. I would be interested
> to know if the behavior with just the preliminary refactoring differs
> at all.
> 

Sure, I can do that. It'll take a couple hours to get the results, I'll
share them when I have them.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Commits

  1. Fix bitmapheapscan incorrect recheck of NULL tuples

  2. Increase default maintenance_io_concurrency to 16

  3. Separate TBM[Shared|Private]Iterator and TBMIterateResult

  4. Improve read_stream.c advice for dense streams.

  5. Increase default effective_io_concurrency to 16

  6. Delay extraction of TIDBitmap per page offsets

  7. Add lossy indicator to TBMIterateResult

  8. Move BitmapTableScan per-scan setup into a helper

  9. Add and use BitmapHeapScanDescData struct

  10. Fix bitmap table scan crash on iterator release

  11. Bitmap Table Scans use unified TBMIterator

  12. Add common interface for TBMIterators

  13. Make table_scan_bitmap_next_block() async-friendly

  14. Move EXPLAIN counter increment to heapam_scan_bitmap_next_block

  15. Refactor tidstore.c iterator buffering.

  16. BitmapHeapScan: Remove incorrect assert and reset field

  17. Change BitmapAdjustPrefetchIterator to accept BlockNumber

  18. BitmapHeapScan: Use correct recheck flag for skip_fetch

  19. BitmapHeapScan: Push skip_fetch optimization into table AM

  20. BitmapHeapScan: postpone setting can_skip_fetch

  21. BitmapHeapScan: begin scan after bitmap creation

  22. Fix EXPLAIN Bitmap heap scan to count pages with no visible tuples

  23. Remove redundant snapshot copying from parallel leader to workers

  24. Remove some obsolete smgrcloseall() calls.

  25. Remove the "snapshot too old" feature.

  26. Compute XID horizon for page level index vacuum on primary.