public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
To: Andres Freund <[email protected]>
To: Pg Hackers <[email protected]>
To: Thomas Munro <[email protected]>
To: Heikki Linnakangas <[email protected]>
To: Nazir Bilal Yavuz <[email protected]>
Subject: Re: BitmapHeapScan streaming read user and prelim refactoring
Date: Wed, 28 Feb 2024 14:22:29 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <20240227142230.nu3ytvcjwouvczlt@liskov>
References: <CAAKRu_ZwCwWFeL_H3ia26bP2e7HiKLWt0ZmGXPVwPO6uXq0vaA@mail.gmail.com>
<[email protected]>
<CAAKRu_bQ9a2dB42Tz-mrsR+2MuG-ojRmqbX-x-TC08Uo_RunNw@mail.gmail.com>
<20240216173559.xiy5xcl5dqmsprns@liskov>
<20240227015028.knohvy3spaqwk7lf@liskov>
<20240227142230.nu3ytvcjwouvczlt@liskov>
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.
Attached is PDF summarizing the results as a pivot table, with results
for "master" and "patched" builds. The interesting bit is the last
column, which shows whether the patch makes it faster (green) or slower
(red).
The results seem pretty mixed, on both machines. If you focus on the
uncached results (pages 4 and 8-9), there's both runs that are much
faster (by a factor of 2-5x) and slower (similar factor).
Of course, these results are with forced bitmap scans, so the question
is if those regressions even matter - maybe we'd use a different scan
type, making these changes less severe. So I logged "optimal plan" for
each run, tracking the scan type the optimizer would really pick without
all the enable_* GUCs. And the -optimal.pdf shows only results for the
runs where the optimal plan uses the bitmap scan. And yes, while the
impact of the changes (in either direction) is reduced, it's still very
much there.
What's a bit surprising to me is that these regressions affect runs with
effective_io_concurrency=0 in particular, which traditionally meant to
not do any prefetching / async stuff. I've perceived the patch mostly as
refactoring, so have not really expected such massive impact on these cases.
So I wonder if the refactoring means that we're actually doing some sort
amount of prefetching even with e_i_c=0. I'm not sure that'd be great, I
assume people have valid reasons to disable prefetching ...
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
[application/pdf] bitmapscan-results.pdf (483.7K, ../[email protected]/2-bitmapscan-results.pdf)
download
[application/pdf] bitmapscan-results-optimal.pdf (177.6K, ../[email protected]/3-bitmapscan-results-optimal.pdf)
download
[application/x-shellscript] bitmapscan-test.sh (7.2K, ../[email protected]/4-bitmapscan-test.sh)
download
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: BitmapHeapScan streaming read user and prelim refactoring
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox