Message-ID: <2abf54a409ea1a3aac778040cf2035d2b9230e22.camel@cybertec.at>
Subject: Re: pg_stats.correlation rule of thumb for re-clustering a table?
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ron Johnson <ronljohnsonjr@gmail.com>, pgsql-admin
	 <pgsql-admin@postgresql.org>
Date: Fri, 12 Sep 2025 22:59:07 +0200
In-Reply-To: 
 <CANzqJaCEvOm_zY7Ex_vN3r5cQLzWx9DxYd3hV=pwPmm4wsJGWQ@mail.gmail.com>
References: 
	<CANzqJaCEvOm_zY7Ex_vN3r5cQLzWx9DxYd3hV=pwPmm4wsJGWQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/2abf54a409ea1a3aac778040cf2035d2b9230e22.camel%40cybertec.at>
Precedence: bulk

On Fri, 2025-09-12 at 10:46 -0400, Ron Johnson wrote:
> Purely OLTP tables (that are only accessed randomly) can of course live w=
ith 0% correlation,
> but lots of tables are mixed-use, and so benefit from physical ordering o=
n=C2=A0a carefully=C2=A0chosen field..
>=20
> SELECT abs(correlation)::numeric(3,2) as correlation
> FROM pg_stats=20
> WHERE schemaname =3D 'foo' AND tablename =3D 'bar'
> =C2=A0 AND attname =3D 'blarge';
> =C2=A0correlation
> -------------
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 0.84
> (1 row)
>=20
> Obviously 84% is no need to worry, but what about 60% or 40%? Currently, =
I use 60%, but would like to do better.

Either the difference is gradual, so that it there is no real cut-off point=
,
or there is a sudden plan change at some point that depends on the query th=
e
data and the parameter settings.  I don't think it is possible to give reli=
able
numbers that cover all cases.

I suggest that you run a series of benchmarks with a copy of the table with
different correlation values and come up with numbers that are meaningful
for your individual case.

Yours,
Laurenz Albe