Message-ID: <4b751bc5-446f-4357-a1e1-827585bf22b0@enterprisedb.com>
Date: Wed, 28 Feb 2024 15:56:06 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BitmapHeapScan streaming read user and prelim refactoring
Content-Language: en-US
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>
References: 
 <CAAKRu_ZwCwWFeL_H3ia26bP2e7HiKLWt0ZmGXPVwPO6uXq0vaA@mail.gmail.com>
 <20240214194228.jf4o7luxtyra5ggv@awork3.anarazel.de>
 <CAAKRu_bQ9a2dB42Tz-mrsR+2MuG-ojRmqbX-x-TC08Uo_RunNw@mail.gmail.com>
 <20240216173559.xiy5xcl5dqmsprns@liskov>
 <20240227015028.knohvy3spaqwk7lf@liskov>
 <20240227142230.nu3ytvcjwouvczlt@liskov>
 <40c213cc-6a15-4e2c-9e56-dae344d52a73@enterprisedb.com>
 <CAAKRu_ar_C+RCMV_LrCL10ccWA0n-0rZcxfhL67hXFqREpPRfg@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: 
 <CAAKRu_ar_C+RCMV_LrCL10ccWA0n-0rZcxfhL67hXFqREpPRfg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/4b751bc5-446f-4357-a1e1-827585bf22b0%40enterprisedb.com>
Precedence: bulk

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