MIME-Version: 1.0
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 12 Sep 2025 10:46:30 -0400
Message-ID: 
 <CANzqJaCEvOm_zY7Ex_vN3r5cQLzWx9DxYd3hV=pwPmm4wsJGWQ@mail.gmail.com>
Subject: pg_stats.correlation rule of thumb for re-clustering a table?
To: pgsql-admin <pgsql-admin@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c9a4fd063e9bb841"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaCEvOm_zY7Ex_vN3r5cQLzWx9DxYd3hV%3DpwPmm4wsJGWQ%40mail.gmail.com>
Precedence: bulk

--000000000000c9a4fd063e9bb841
Content-Type: text/plain; charset="UTF-8"

(By re-cluster, I of course mean pg_repack.)

Purely OLTP tables (that are only accessed randomly) can of course live
with 0% correlation, but lots of tables are mixed-use, and so benefit from
physical ordering on a carefully chosen field..

SELECT abs(correlation)::numeric(3,2) as correlation
FROM pg_stats
WHERE schemaname = 'foo' AND tablename = 'bar'
  AND attname = 'blarge';
 correlation
-------------
        0.84
(1 row)

Obviously 84% is no need to worry, but what about 60% or 40%? Currently, I
use 60%, but would like to do better.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--000000000000c9a4fd063e9bb841
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>(By re-cluster, I of course mean pg_repack.)</div><di=
v><div><br class=3D"gmail-Apple-interchange-newline">Purely OLTP tables (th=
at are only accessed randomly) can of course live with 0% correlation, but =
lots of tables are mixed-use, and so benefit from physical ordering on=C2=
=A0a carefully=C2=A0chosen field..</div><div><br></div></div><div><font fac=
e=3D"monospace">SELECT abs(correlation)::numeric(3,2) as correlation<br>FRO=
M pg_stats <br>WHERE schemaname =3D &#39;foo&#39; AND tablename =3D &#39;ba=
r&#39;<br>=C2=A0 AND attname =3D &#39;blarge&#39;;</font></div><div><font f=
ace=3D"monospace">=C2=A0correlation<br>-------------<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 0.84<br>(1 row)<br></font></div><div><br></div><div>Obviously 84=
% is no need to worry, but what about 60% or 40%? Currently, I use 60%, but=
 would like to do better.</div><div><br></div><span class=3D"gmail_signatur=
e_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature" data-sma=
rtmail=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and =
butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;=
Redacted&gt; lobster!</div></div></div></div></div></div>

--000000000000c9a4fd063e9bb841--