Feedback-ID: ia2694551:Fastmail
Date: Wed, 19 Jun 2024 19:09:26 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Amit Langote <amitlangote09@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>, 
	Andres Freund <andres@anarazel.de>, Daniel Gustafsson <daniel@yesql.se>, 
	David Rowley <dgrowleyml@gmail.com>, Jacob Champion <jchampion@timescale.com>, 
	PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Thom Brown <thom@linux.com>, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: generic plans and "initial" pruning
Message-ID: <202406191709.jbvpf7d7hl6g@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CA+HiwqFpZ80UJKr4tZus4Omgg7YESzFXKSwSHRW2Ap2=XSVyUA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/202406191709.jbvpf7d7hl6g%40alvherre.pgsql>
Precedence: bulk

I had occasion to run the same benchmark you described in the initial
email in this thread.  To do so I applied patch series v49 on top of
07cb29737a4e, which is just one that happened to have the same date as
v49.

I then used a script like this (against a server having
plan_cache_mode=force_generic_mode)

for numparts in 0 1 2 4 8 16 32 48 64 80 81 96 127 128 160 200 256 257 288 300 384 512 1024 1536 2048;  do
    pgbench testdb -i --partitions=$numparts 2>/dev/null
    echo -ne "$numparts\t"
    pgbench -n testdb -S -T30 -Mprepared | grep "^tps" | sed -e 's/^tps = \([0-9.]*\) .*/\1/'
done

and did the same with the commit mentioned above (that is, unpatched).
I got this table as result

 partitions │   patched    │  07cb29737a  
────────────┼──────────────┼──────────────
          0 │ 65632.090431 │ 68967.712741
          1 │ 68096.641831 │ 65356.587223
          2 │ 59456.507575 │ 60884.679464
          4 │    62097.426 │ 59698.747104
          8 │ 58044.311175 │ 57817.104562
         16 │ 59741.926563 │ 52549.916262
         32 │ 59261.693449 │ 44815.317215
         48 │ 59047.125629 │ 38362.123652
         64 │ 59748.738797 │ 34051.158525
         80 │ 59276.839183 │ 32026.135076
         81 │ 62318.572932 │ 30418.122933
         96 │ 59678.857163 │ 28478.113651
        127 │ 58761.960028 │ 24272.303742
        128 │ 59934.268306 │ 24275.214593
        160 │ 56688.790899 │ 21119.043564
        200 │ 56323.188599 │ 18111.212849
        256 │  55915.22466 │ 14753.953709
        257 │ 57810.530461 │ 15093.497575
        288 │ 56874.780092 │ 13873.332162
        300 │ 57222.056549 │ 13463.768946
        384 │  54073.77295 │ 11183.558339
        512 │ 37503.766847 │   8114.32532
       1024 │ 42746.866448 │   4468.41359
       1536 │  39500.58411 │  3049.984599
       2048 │ 36988.519486 │  2269.362006

where already at 16 partitions we can see that things are going downhill
with the unpatched code.  (However, what happens when the table is not
partitioned looks a bit funny.)

I hope we can get this new executor code in 18.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)