Message-ID: <a7e4e5b855ef94b2dd16a59adc794b670e682e27.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>
Cc: pgsql-performance@postgresql.org
Date: Mon, 23 Feb 2026 21:54:04 +0100
In-Reply-To: <A92040E8-9AFC-4B41-AB19-88E09ACB71AF@gmx.net>
References: <ECA94B5A-E5B4-45F7-B1B4-9BF754083164@gmx.net>
	 <e94e4d006975792d72e667bbd0499f9ddec4ed58.camel@cybertec.at>
	 <1695A676-062B-47C5-B302-91E2357DC874@gmx.net>
	 <b2e372392b8a022da81b95b7c823a5729d7fd70f.camel@cybertec.at>
	 <A92040E8-9AFC-4B41-AB19-88E09ACB71AF@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/a7e4e5b855ef94b2dd16a59adc794b670e682e27.camel%40cybertec.at>
Precedence: bulk

On Mon, 2026-02-23 at 21:42 +0100, Attila Soki wrote:
> > > plan-ok:
> > > https://explain.depesz.com/s/hQvM
> > >=20
> > > plan-wrong:
> > > https://explain.depesz.com/s/uLvl
> >=20
> > Thanks.
> >=20
> > The difference in the plans is under the "Subquery Scan on odg", starti=
ng with
> > plan node 50 (everything under the "Sort"). =C2=A0I suspect that the mi=
s-estimate
> > that is at the root of the problem is here:
> >=20
> > -> =C2=A0Index Scan using table_k_late_spec_dp_end_dat_key on schema1.t=
able_k kal =C2=A0(... rows=3D196053 ...) (... rows=3D471.00 ...)
> > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Index Cond: (kal.dp_end_dat < ('now'::cst=
ring)::date)
> > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Index Searches: 1
> > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Buffers: shared hit=3D230 read=3D49
> > =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0I/O Timings: shared read=3D0.142
> >=20
> > PostgreSQL overestimates the row count by a factor of over 400.
> > Try to fix that estimate and see if that gets PostgreSQL to do the righ=
t thing.
> >=20
> > Perhaps a simple ANALYZE on the table can do the trick.
>=20
>=20
> In the examples I used table_k to flip the plan with
> vacuumed -Upostgres -vZ -t schema1.tbl_used_in_query db1
> in the explain output schema1.tbl_used_in_query is table_k

I cannot understand that.

> > The right side of the comparison looks awkward, as if you wrote 'now'::=
text::date
> > My experiments show that PostgreSQL v18 estimates well even with such a=
 weird
> > condition, but perhaps if you write "current_date" instead, you'd get b=
etter results.
>=20
> I didn't realize that made a difference. I will replace all occurrences. =
It also looks more clean with current_date.

It *didn't* make a difference when I played with that...

> > I'd play just with a query like
> >=20
> > =C2=A0EXPLAIN (ANALYZE)
> > =C2=A0SELECT * FROM schema1.table_k AS kal
> > =C2=A0WHERE dp_end_dat < current_date;
> >=20
> > until I get a good estimate.
>=20
> I will try to set custom statistics for dp_end_dat and the fields used by=
 the=C2=A0table_k_late_spec_dp_end_dat_key index.
> Let=E2=80=99s see if that helps.

For a simple condition like that, extended statistics won't help.

That's why I suggested a plain ANALYZE.
I am not sure why that is estimated so badly.

Yours,
Laurenz Albe