Horton

Re: generic plans and "initial" pruning

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
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>
Date: 2024-06-19T17:09:26Z
Lists: pgsql-hackers 
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)

Commits

  1. Stamp 19beta1.

  2. Revert "Don't lock partitions pruned by initial pruning"

  3. Ensure first ModifyTable rel initialized if all are pruned

  4. Fix bug in cbc127917 to handle nested Append correctly

  5. Remove unstable test suite added by 525392d57

  6. Don't lock partitions pruned by initial pruning

  7. Fix an oversight in cbc127917 to handle MERGE correctly

  8. Track unpruned relids to avoid processing pruned relations

  9. Perform runtime initial pruning outside ExecInitNode()

  10. Move PartitionPruneInfo out of plan nodes into PlannedStmt

  11. Fix setrefs.c's failure to do expression processing on prune steps.

  12. Remove obsolete executor cleanup code

  13. Revert "Move PartitionPruneInfo out of plan nodes into PlannedStmt"

  14. Move PartitioPruneInfo out of plan nodes into PlannedStmt

  15. Refactor and cleanup runtime partition prune code a little

  16. Remove some unnecessary fields from Plan trees.

  17. Remove more redundant relation locking during executor startup.

  18. Shut down Gather's children before shutting down Gather itself.