From: Attila Soki <atiware@gmx.net>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: unstable query plan on pg 16,17,18
Message-Id: <ECA94B5A-E5B4-45F7-B1B4-9BF754083164@gmx.net>
Date: Mon, 23 Feb 2026 10:37:01 +0100
To: pgsql-performance@postgresql.org
UI-OutboundReport: notjunk:1;M01:P0:B+gi0E3XaTU=;HxKkiAsApJF200O2vkdQOriAYsd
 xskKYDEaG+FSgIzavafc4C681EhS0wKF3rNZCjVmmI7l/WNVvDXTL4JT1WVj3B1MfRXdWWBmX
 Sjezj1nqyoLEH859aTwfladaghSHlFxusmeMWulrVIPhExtbcfEDj5uCMjghGTb8laltdWDgJ
 cDGm8/1qVcvPIJs/GaOM9WPrzsevEBygmkhJ6zTIvMopd/0BjNwinEpiX/ig/CZuvvyTn+fEw
 ek7kl8wA5Z8BtunyiATcGQ4Vgv/J+bXMPS/A+/PgVLBmC6Y7xtfrp3L5UhYeC9aZOIdShZSLP
 0cO7lmPilLaiUkqOehUXwZKgGpm/aHDvD7vqYFo6Pj5aJghMl7afkQ2uuAxrrj504t6ArwAOP
 JNURMW/rkKSiWh0XbK1kYqL6dEFMIxcO3s02ZmG94tW1mbq/piZ0aiaHLcYcrT4jUqmJ2SFQf
 otJHFf8ZJElftb9GNvc4EG1FVFtfQ5qAGrf30Cx4MlFriSUgw39PE3DfJzxTGoAA+zaRAOS7r
 a2ay3ZXm7Ul9BcSH01ZOy6B9l+CKNM2h+mUJvkOeTAXCAzDW5v+x6oByAW231oUh9XyPryPy8
 1sCIxRpXJrF7sbIbr/WVUZm39xS/HQ/rRwvu7VsBhlSm5YO5eNQ0gK9kdq5avjw7M0xnGOK9q
 U1zo+c4mQr4fSwD566a8TBEw9vHqksIVnaZxnJuDCDvRC+goVIDn0El7LE+9pg90fSWeAyvIM
 yVadD9DGMK9RE8KFdxzRgp+6Fka1U56VhzI7QiOTe0Fl7GXRQGWvTt6g4IBvrcSMLRYvhRVet
 ZF2uGGIP7jHdqUEmvZoNxZg+tTwz4RWsYqJcH8acMpPTOhasxRNDJ7cqJgAjQb9W3BQ/cqLa/
 +3+Hq+tizVxrJlldRk9ydcIw/QuJbChQm3gCm7i1qtxQDDg3NGyzfMeSAA2Dv1akb121ZLxu7
 iZlU9uRDvK0Svk/657iCG2HEgNlmNCiMUCJ5gcD+u44mYYXWmxa9HY2dKbMgRAALFoERqUptS
 95JRvoKeOJE+vzNOxx9Pnr2dnevarvJ8p1h1jvWA1TZ+i1CZqtBbAgvlZt31fOCbf1H7G0Ymt
 MvtmfEdEiJ559n7Vq2L9tcN3TDyVPffkV6xjYdiC/eI6ICDJky4LhUv6KpuCZ4RDGshj0KDS3
 1EH7tOu3XRbalCF5C7jv1zT9Eed8PmUVYterrfuMp/S/gSGLjliUlva2x+SsWUe0FVyrPY+Xg
 EBPDyJeFUCiTPkrCGVWl41SjRdhe5sOF2nK/Kpu1WyGFyvipEARh3KTti2znc1NGA9pkQfn+u
 MKxzxM+ab1A5glTeL4PCoEFWIoIsIEfIYSksT/p7p44bA9m2s2oAc88xJxQmdbb+KPCKR/SdT
 4Ejw/lk/ic0mMVegwPJxlswYDqrp6j8ZBdDkdGiusgKun+0h7E/6HV8dj9ZlQJBO4db9dVzv/
 +m/j4aGlC/7rwHFoHdk+kHIXSEX1h+HwnSmU07CYnpAMIeYvA2QOwEo2R4fbS3LZbQq9CAW65
 N7PAsSUVmBkYVr1ABqZQXuTyCboIzFZR4yscIzLIVaim1eSbjhdaM2n4BieccRdDUb2Itc0F6
 RD728k36eQMq1dTuC3EM/9UmxilEjA78czscp9r4S38db5BqkYa8N2vR2deI9p2RqvdIurFvr
 TR6gzU52oVNUx7JUvnw6wtfWC4RI3sfxY2w6Mu/sQkEJyk2OhjMRtHBldoySffPYsV+naWsQb
 QXS8bCkXqdSKGYhL06M/9OFprytmVCGhg3fUTxT5LYRcm0o7dy8APUWwl9BE4yzh9Tl6cJDx5
 b/ummdUJ3iOYqfjqH5hDhqW+pRGUg3WoC5zZ+HNehi/4p4zOxWjle54WnACu7fBQEB1fqMUfV
 R6ek4DYv0Mt/9vCmg/jJa/qGCYDOsvqbiSFASvTW4vpAl7b3GQzL10flFN/9t+6XttcTEg0vV
 qInxT3clT7aBJcwe7fwYA1H5lViL9FAodbOTbTeI9P4yq83i3IjWdCt/VzKBft/EpEmnI9+ln
 lzSRVxVkzmU/R0SE3KbdUjCUpFFom41MwV9GmbvcNZg/G3kUDG7teLl/v7nmjBnkXCGzd4IFf
 1h3ds7FFO+iPneH1WM1fF7mriDqsTJ6Uh+j+LkDf1OLxKDlaDOEYkyXwZfLnqfMJBNWJer3sw
 5j7tvDO9lRZI2f+HqvBEUHOlijJ7xiqkQvv9I4honrAKIOQ5xlX0qozZIG+Qy6Dz3wCICvC7+
 oOa8w18Rxjw49gs2XgFMim5kU10lsFDMLaOj2K7uT5D+vvaZDvL6YFiFCfPOiopK9VLrDwob6
 81TSHUcaleWulvYn4zk50auVUrERLfggl5yVjl7jpbNLO0B8eXUIRDuKg/Htq5LBy4UCmEY75
 7o+LqHDW9+YpccusbFMSsHQK0uQAh+I9168dbKAcJ3yLeR09r9gs+oy8DVYvDTY62Wh6e144/
 1qWPyPcJvCQReB0nm5THp7fponMdefIiMDvP0FWjOWqmoGG/GC+2nKZXuwI2jP1RFIPCLnZmo
 izafgRgMDIqvrz+1gnPEKGzoj0s4AhAvJRHrVr1PyXswoiuDPRZuCmCjaWE2bqLk5kgGAhrIR
 2/CtPVEi3cP9LSAwvaY1Py29EY8Uv2UakNZZNLXGXywrCKVuQnlWxeuP5ZcKQpEtmr6Ezkr/q
 C3SxvAd7ZFw8jioa8gVqqJwKgAS7weE7/UXVhqApH87TK0D8zvh+K4EMUJzS3RNhYk69N33yP
 KOMg+7jWieLVgwsk6VOgu/XV8M8iYJTrnBftQ0xfwHHMgihcHC2uia6K0/hW7qTkQ3crca3XC
 qNOM5Veipod+n/zBtBusVGQChMlQz85GqHtQk5vj/eHxw4baF3Lmhg8rfkUt7Wc2cIwk18EyJ
 Wv0fIgz+KnAsd8prJwfK4UyIRBbQ7RnzDh2XbcA7lLBBBx1IRS/4YzaeYA8+H8Po9/1apiqcM
 qLZ0dAXjksp2ujwrbfzvQOEuBt+ww1VcDhy6rZZAdLSZgALlnIveo/jt4qacPo85Es2RG8UFu
 gKm3nkqV5WutNorv1sTi8HuOZ257MbTAV45mCFuq8yUX9ei20Ha/kZHx0E+09SPGI50QRQmMM
 szWclW0wlYbKFiRL0SqdOhFJbWDAWC+3YcGRfKBDslZScfOCy+H0C3YO5KBkgo8EhlBOoTGHJ
 AMsgaPxoyDe+rZri1TQ8SYZ9xjTSU6p0TdiNPkB8DwOd2qdrQPlZmPjlMYIRahaS+9RglK+AA
 PAKBfqzZ4yEJUvp6Sl9CaxY7IQLPE886n81UxxaipdG2vUaZ1/lLRfNQfgGCt9I7UN0Axw0aT
 W5+qA9IyM9sZeoD9E95gwakTav5QcricCKdmrowY7JqORk33DSAQ3Frs5DuMBuYPUt0P/WcOP
 nKTBcfDu5uk/8DH2h8pQECOMvfseDyW2D0zqvHI3cVI7WpG5XoC1f+7NLV523uFFDxckhrd1/
 eDKO2wViJCi3GNiMoQgpGDNH2aG63JfiSt1qHF2hLFs3fmILuOyMCHBsg8jqP2euWoQDwrgdj
 WOAGQCXcQge5IULPpSvi2O6EBzMk6fBQU7xz3033bkwjsJUR8a0ccxJdtNhnLy9sq8cULOgst
 GscZDLeEqv4RvtlnU1z5tot2qRqP2VdIreWo+LUKIPYzyzvL9ext/ofTohmTpMnkaauZOVp19
 Y8YYfL8F8W2PRpr4HEqAVP4gCx8vcoBEj6I+hpKa89up3NLSCzol1VZbZQvTcP/7YpPQ0TMHv
 5B2JHIoOci6lSvfs7d1L19cYyYqR5HlDSo/VMi3cen9fqbyMHbABnGbalxnPpISYNfImxOtpd
 PJKVkoR/l8qT6uAVHYDmzb0gi7QLqkbYQtpa+OXuIFLr3zWndK53/V7YpXKkMASDJ++VsGFN2
 JMBxKf+C7HWU0A2USADmtw5iM3yPTXDlRo7Aw3+Ar1RkMstqovCTSi0BInRLz1EevXhL9kDa0
 wf42aPL20ODMVAXY5HQZ6NNxd5Brbr1qFaXMdHooyh9mkd/xaFgNjDQMh4b+7pWujp9tdWHll
 axyEwk4v+eJV/0DXoGAoH1BZ2G8y3oOjyRhxyHKl3WKI8/Fzj9yIk4++C9guRDfLthMnxkuPr
 S12/Y5HunXkNxPvZO602xfh9qfXQbhPw68Veq9MfLJPD1hT/VUvx2rN9a1h6v3L2o27xUXoiU
 4oksXEOTH0jPxU2nAziUST4l94t1A1pdk7CKGGOnKALfsyVdBuDgOrZUfn4ZxRjVB68C40DUB
 XJRRugcOqV1EFgDHUH+8xuDwxgyQBUTeTmjDt2oCU+c6fTIYB/zcM/NBOHCH90K7mvGa8e7x/
 uHZINe36GLldpfJxwH14Z9cQLMxnZWLNdoy4mttBzrNDL1vomraZZzn9m4xvkGw+F4gOyEPWh
 gdlj6B9kBUPkGwR5N4PaW0GiTY9KDES2hlMYJLzFr1GfsJ9Tl0q90XB38Q57SoiFxe4LpBK5G
 oGpjXzoJ9rgEdoqEAbvqiUrMO65Lp7k6b1ZLbbUlmWzLQzeQapmYhegmaUdB6Qa743YKljo06
 buvOjYrFsphgLxqLjxDt4zIhnnrITsQc4HBvNKDDhcsBduzk3p7h9lYJk8pi6++Yfgj+cQysR
 J+lo0GqWlqMQOP5/hCtWxzVoqPr6bwPB2tu8re/3auHa3Je72D9W5gyl8SXlXMyHc6XLpnfk+
 7E7rI2N7pI5vY/L4Z5swc6pMbxQSWr2qcjdVYJ1odLLrLftgdPnGrfasniXEJRe6ytsXUTpRh
 IjSZKPanpUOJ+78mDStIV6yvYW9TfpdF2T/Gw5sYhyJAxcbRArD8c0OS2/BcJjodC7vBB+KSe
 gajqgXwRYPM1uvIaPRDV711GMyO4gMSmSumY9wPMLrWixOQsfmVGxAyOc2hziY6goN4MGQbJ+
 Fyh6O8ZAimbjkD8AYl9YfjfAhW4ZwNWG4gh42Ijj1AGiV4hZ1vBuXwmS/a4aR+rP/gmVwKH+Z
 nRARLcmqFHZfUmsexEechxs0EXl4/smZLAaJ+IHvH5CAznT7E5fZ2j/ziYNH+1lkK2bCpYa9M
 Ly+toGjiNDiGodGJVKT9iBxC0xTeg0ODI94EerTpNDzbyvp8J8MEsk4RHQpRZCTYa3f2o0QwS
 hkFZm/+IMAte/SnjU=
Archived-At: 
 <https://www.postgresql.org/message-id/ECA94B5A-E5B4-45F7-B1B4-9BF754083164%40gmx.net>
Precedence: bulk

Hi there,

When upgrading from PostgreSQL 14.4, I noticed that one of my somewhat =
complex analytical queries sometimes gets an inefficient plan under =
PostgreSQL 16, 17, and 18.
Under 14.4, the query runs with a stable plan and completes in 19 to 22 =
seconds. In newer versions, the plan seems to be unstable, sometimes the =
query completes in 17 to 20 seconds, sometimes it runs for 5 to 18 =
minutes with the inefficient plan.
This also happens even if the data is not significantly changed.

When I was preparing this email last week, I saw another thread on a =
similar topic. This email is unrelated to that thread.

After some experiments, I have now 4 cloned VM's one for each pg =
version. For pg 14.4, 16.11, 17.6, 18.2 and I can now more or less =
reproduce the plan flip. I use the vacuumdb command to trigger a flip, =
but under "production-like-test" conditions it seems to flip randomly.
To test I created a bash script to run the analytical query via psql in =
an endless loop. The script prints out the runtime or after 28 seconds =
the query will be aborted.
I also tried to get an inefficient plan on pg 14.4, for that, I must set =
default_statistics_target to 9 or less.
While pg 14.4 chooses (so far) always the inefficient plan with =
default_statistics_target < 10, pg 18.2 seems to be able to randomly =
produce the efficient plan with default_statistics_target=3D1 too.

My questions are so far, where to begin, how can I find out why the plan =
changes and how can I find out what I must change to stabilize the plan.
Any advice is welcome. I can provide some more info if needed, I can =
also recompile pg with debug, change settings, etc. if that helps.

The environment:
Postgresql runs in a development VM with 6 Core, 16 GB RAM.
OS is Debian 13: Linux d13pg18 6.12.63+deb13-amd64 #1 SMP =
PREEMPT_DYNAMIC Debian 6.12.63-1 (2025-12-30) x86_64 GNU/Linux
Postgresql 18.2 compiled from source with the same settings as V14.4 =
(tried so far: 16.11, 17.6, 18.0, 18.1)
I am the only user in VM and on the Host too. During the test, only one =
of the VM is running.
All tables required by the query are cached. (atop: RAM Total 15.6G, =
Free: 12.0G, Cache: 2.7G)
All tables using the default statistics.
The database gets no changes, no other requests.
The analytical query makes no changes to the db (it only reads data, no =
temp tables, no materialized views, no temp files).

Preparation:
- clone VM
- compile and install pg
- initdb
- create database, create role
- set default_statistics_target =3D 170 in postgresql.conf (I tried 1, =
5, 9, 10, 50, 100, 170, 180, 190, 200 with no difference. at 500 I could =
not get an OK plan, or not tried long enough)
- (set other params in conf, mostly like in V14.4, or only for this =
test. see below)
- restart postgres
- psql < dump.sql
- vacuumdb -Upostgres -avz -j5
- restart VM


after that, when I execute "vacuumdb -Upostgres -vZ -t =
schema1.tbl_used_in_query db1" sometimes the plan flips from inefficient =
to OK or back.
Once flipped, it stays stable for multiple runs.
To make it flip again I must execute the above command or "vacuumdb =
-Upostgres -vZa -j5".

The table "schema1.tbl_used_in_query" is one of the tables used in =
query. To trigger the flip the vacuumed table must be one of the tables =
used by the query.

For example, a real case:

After VM restart, the query is running in endless loop. The data is not =
changing.

## 1x run, the first, aborted after 28 sec. (the first run after a =
reboot is always aborted because of uncached data)
## PLAN OK (5x runs)

vacuumdb -Upostgres -vZ -t schema1.tbl_used_in_query db1
vacuumdb: vacuuming database "db1"
INFO:  analyzing "schema1.tbl_used_in_query"
INFO:  "tbl_used_in_query": scanned 51000 of 171971 pages, containing =
977840 live rows and 0 dead rows; 51000 rows in sample, 3297257 =
estimated total rows
INFO:  finished analyzing table "db1.schema1.tbl_used_in_query"
avg read rate: 58.830 MB/s, avg write rate: 0.232 MB/s
buffer usage: 26748 hits, 27651 reads, 109 dirtied
WAL usage: 507 records, 101 full page images, 662069 bytes, 0 buffers =
full
system usage: CPU: user: 3.55 s, system: 0.09 s, elapsed: 3.67 s

## PLAN WRONG (5x runs)

vacuumdb -Upostgres -vZ -t schema1.tbl_used_in_query db1
vacuumdb: vacuuming database "db1"
INFO:  analyzing "schema1.tbl_used_in_query"
INFO:  "tbl_used_in_query": scanned 51000 of 171971 pages, containing =
977837 live rows and 0 dead rows; 51000 rows in sample, 3297247 =
estimated total rows
INFO:  finished analyzing table "db1.schema1.tbl_used_in_query"
avg read rate: 57.663 MB/s, avg write rate: 0.234 MB/s
buffer usage: 26319 hits, 28077 reads, 114 dirtied
WAL usage: 502 records, 101 full page images, 654978 bytes, 0 buffers =
full
system usage: CPU: user: 3.64 s, system: 0.14 s, elapsed: 3.80 s

## PLAN WRONG (5x runs)

vacuumdb -Upostgres -vZ -t schema1.tbl_used_in_query db1
vacuumdb: vacuuming database "db1"
INFO:  analyzing "schema1.tbl_used_in_query"
INFO:  "tbl_used_in_query": scanned 51000 of 171971 pages, containing =
977527 live rows and 0 dead rows; 51000 rows in sample, 3296202 =
estimated total rows
INFO:  finished analyzing table "db1.schema1.tbl_used_in_query"
avg read rate: 61.615 MB/s, avg write rate: 0.253 MB/s
buffer usage: 26604 hits, 27785 reads, 114 dirtied
WAL usage: 416 records, 10 full page images, 205744 bytes, 0 buffers =
full
system usage: CPU: user: 3.38 s, system: 0.12 s, elapsed: 3.52 s

## PLAN OK (22x runs)

vacuumdb -Upostgres -vZ -t schema1.tbl_used_in_query db1
vacuumdb: vacuuming database "db1"
INFO:  analyzing "schema1.tbl_used_in_query"
INFO:  "tbl_used_in_query": scanned 51000 of 171971 pages, containing =
978434 live rows and 0 dead rows; 51000 rows in sample, 3299260 =
estimated total rows
INFO:  finished analyzing table "db1.schema1.tbl_used_in_query"
avg read rate: 55.409 MB/s, avg write rate: 0.229 MB/s
buffer usage: 26670 hits, 27639 reads, 114 dirtied
WAL usage: 477 records, 102 full page images, 655391 bytes, 0 buffers =
full
system usage: CPU: user: 3.75 s, system: 0.13 s, elapsed: 3.89 s

## PLAN WRONG (43x runs)

vacuumdb -Upostgres -vZ -t schema1.tbl_used_in_query db1
vacuumdb: vacuuming database "db1"
INFO:  analyzing "schema1.tbl_used_in_query"
INFO:  "tbl_used_in_query": scanned 51000 of 171971 pages, containing =
978347 live rows and 0 dead rows; 51000 rows in sample, 3298967 =
estimated total rows
INFO:  finished analyzing table "db1.schema1.tbl_used_in_query"
avg read rate: 57.883 MB/s, avg write rate: 0.248 MB/s
buffer usage: 26731 hits, 27584 reads, 118 dirtied
WAL usage: 484 records, 110 full page images, 719407 bytes, 0 buffers =
full
system usage: CPU: user: 3.59 s, system: 0.11 s, elapsed: 3.72 s

## PLAN OK (55x runs)


#postgresql.conf of this VM
escape_string_warning =3D off
lc_messages =3D 'C'
lc_monetary =3D 'C'
lc_numeric =3D 'C'
lc_time =3D 'C'

track_activities =3D on

logging_collector =3D on
log_directory =3D '/usr/local/foo/logs/pg_logs'
log_filename =3D 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_messages =3D info
log_min_error_statement =3D warning
log_temp_files =3D 1

huge_pages=3Dtry
shared_buffers =3D 768MB
temp_buffers =3D 512MB
effective_cache_size =3D 4GB
work_mem =3D 768MB
hash_mem_multiplier =3D 2.5

maintenance_work_mem =3D 1GB
max_stack_depth =3D 4MB
max_locks_per_transaction=3D256

wal_buffers =3D -1

jit =3D off
max_worker_processes =3D 4
max_parallel_workers_per_gather =3D 4
max_parallel_workers =3D 4
effective_io_concurrency =3D 16
checkpoint_completion_target =3D 0.9

seq_page_cost =3D 1.0
random_page_cost =3D 1.2
default_statistics_target =3D 170

vacuum_cost_limit =3D 2000


Thanks

Regards,
Attila Soki