public inbox for [email protected]  

help / color / mirror / Atom feed
From: Tomas Vondra <[email protected]>
To: Peter Geoghegan <[email protected]>
To: Matthias van de Meent <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Jeff Davis <[email protected]>
Cc: benoit <[email protected]>
Cc: Alexander Korotkov <[email protected]>
Subject: Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan
Date: Tue, 28 Nov 2023 13:29:27 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAH2-WzmTHoCsOmSgLg=yyft9LoERtuCKXyG2GZn+28PzonFA_g@mail.gmail.com>
References: <CAH2-Wz=ksvN_sjcnD1+Bt-WtifRA5ok48aDYnq3pkKhxgMQpcw@mail.gmail.com>
	<CAEze2Wgdh5WZzOcUQxTUO45g4qL4oxfVOA8re0MqvKjsamrdFQ@mail.gmail.com>
	<CAH2-WzkExhbc5a7tuVy_pZFFiWZ4RiZTRfwVVM2+bcHUqor9ow@mail.gmail.com>
	<CAH2-WzkEyBU9UQM-5GWPcB=WEShAUKcJdvgFuqVHuPuO-iYW0Q@mail.gmail.com>
	<CAH2-Wz=X4wCT-x4HeM95CTqT147K_bL8mC_5d6TUzLPaHdE1nw@mail.gmail.com>
	<CAH2-Wz=_FQ0AVgoF+UT7EzafVyd7kanQ0-82BesrpjUHPtjLvg@mail.gmail.com>
	<CAH2-WzmjKdovfd-T+o0VaboZCdKnccXSvN4cub3qZw=baC_hyA@mail.gmail.com>
	<CAEze2Whps99tdDfDC4hrL92W1oiZBnFXqv1OB55OMdPXtx6yuA@mail.gmail.com>
	<CAH2-WzmHWR=gqeuq4rHDTv4zHyXDXNTSNxYQKYru4vyaUk=FTQ@mail.gmail.com>
	<CAEze2Wj-EBt8ECp7QScx5bBw4VNg=9T==o=Yh=gUR4__C3h=uA@mail.gmail.com>
	<CAH2-Wz=Ct9fLcN-2SV0gx_iBk87RsAi+Sor3OwR2_5Rg_KO0+g@mail.gmail.com>
	<CAEze2WiR5eJgVqDHCEaO0=mDiUyXY_o-8aK-PTKVPCKba_E4Vg@mail.gmail.com>
	<CAH2-WzmTHoCsOmSgLg=yyft9LoERtuCKXyG2GZn+28PzonFA_g@mail.gmail.com>

On 11/21/23 03:52, Peter Geoghegan wrote:
> On Sat, Nov 11, 2023 at 1:08 PM Matthias van de Meent
> <[email protected]> wrote:
>> Thanks. Here's my review of the btree-related code:
> 
> Attached is v7.
> 

I haven't looked at the code, but I decided to do a bit of blackbox perf
and stress testing, to get some feeling of what to expect in terms of
performance improvements, and see if there happen to be some unexpected
regressions. Attached is a couple simple bash scripts doing a
brute-force test with tables of different size / data distribution,
number of values in the SAOP expression, etc.

And a PDF visualizing the comparing the results between master and build
with the patch applied. First group of columns is master, then patched,
and then (patched/master) comparison, with green=faster, red=slower. The
columns are for different number of values in the SAOP condition.

Overall, the results look pretty good, with consistent speedups of up to
~30% for large number of values (SAOP with 1000 elements). There's a
couple blips where the performance regresses, also by up to ~30%. It's
too regular to be a random variation (it affects different combinations
of parameters, tablesizes), but it seems to only affect one of the two
machines I used for testing. Interestingly enough, it's the newer one.

I'm not convinced this is a problem we have to solve. It's possible it
only affects cases that are implausible in practice (the script forces a
particular scan type, and maybe it would not be picked in practice). But
maybe it's fixable ...


regards

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

Attachments:

  [application/pdf] saop-benchmark.pdf (245.4K, ../[email protected]/2-saop-benchmark.pdf)
  download

  [application/x-shellscript] run-all.sh (470B, ../[email protected]/3-run-all.sh)
  download

  [application/x-shellscript] run.sh (3.5K, ../[email protected]/4-run.sh)
  download

view thread (12+ 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], [email protected], [email protected], [email protected]
  Subject: Re: Optimizing nbtree ScalarArrayOp execution, allowing multi-column ordered scans, skip scan
  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