Message-ID: <da00062f06e909ebd9250edcea9fee58bb626392.camel@cybertec.at>
Subject: Re: unstable query plan on pg 16,17,18
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Attila Soki <atiware@gmx.net>, Andrei Lepikhov <lepihov@gmail.com>
Cc: pgsql-performance@postgresql.org
Date: Thu, 26 Feb 2026 21:53:57 +0100
In-Reply-To: <33A1B02B-1FDD-4F5F-8370-26DA72490009@gmx.net>
References: <ECA94B5A-E5B4-45F7-B1B4-9BF754083164@gmx.net>
	 <e94e4d006975792d72e667bbd0499f9ddec4ed58.camel@cybertec.at>
	 <64ddfe83-317c-4cf1-b04f-cb74d9f40629@gmail.com>
	 <529E2365-6C3F-4BDA-9625-312F3A023C5B@gmx.net>
	 <5bace3e5-3d6b-4f81-92a9-9ff4e9e5de53@gmail.com>
	 <1C445A2F-3256-4F04-B55D-9850581FF39A@gmx.net>
	 <0dd76506-053e-4a30-9f95-9dc324e8e1fb@gmail.com>
	 <FF816253-DD7D-4AC2-8CD1-8A5ACB1D1113@gmx.net>
	 <1388B912-7E9A-475A-93AA-67FB99A084BC@gmx.net>
	 <0c8b9845-a2ac-4613-8075-45dc55911143@gmail.com>
	 <45AA83DB-A6B4-43A2-879B-290E4A7845AE@gmx.net>
	 <33A1B02B-1FDD-4F5F-8370-26DA72490009@gmx.net>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/da00062f06e909ebd9250edcea9fee58bb626392.camel%40cybertec.at>
Precedence: bulk

On Thu, 2026-02-26 at 17:15 +0100, Attila Soki wrote:
> Increasing the statistics of dp_end_dat did not helped. With statistic 10=
00
> I was not able to get a good plan without setting join_collapse_limit=3D7

Reducing "join_collapse_limit" dumbs down the optimizer, so you are getting=
 a good plan
by accident.  I mean, you can try to rewrite the query so that the tables a=
re written
in the order in which they should be joined in the good plan, then set "joi=
n_collapse_limit"
to 1.  That may be a solution if you cannot find a better one.

Still, the bad estimate that I indicated in [1] is worrysome, and I don't q=
uite
understand it.  Could you show the result of the simplified query that I su=
ggested?

  EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
  SELECT * FROM schema1.table_k AS kal
  WHERE dp_end_dat < current_date;

If I were you, I'd focus on getting PostgreSQL to estimate that correctly.

Yours,
Laurenz Albe

 [1]: https://postgr.es/m/b2e372392b8a022da81b95b7c823a5729d7fd70f.camel%40=
cybertec.at