From: Mark Frost <FROSTMAR@uk.ibm.com>
To: "pgsql-performance@lists.postgresql.org"
	<pgsql-performance@lists.postgresql.org>
Thread-Topic: [EXTERNAL] Re: Poor row estimates from planner, stat
 `most_common_elems` sometimes missing for a text[] column
Thread-Index: AQHb1ikCQw3lZBlZCEqDxo53AemQGrP0zMQAgAEOsPw=
Date: Fri, 6 Jun 2025 09:21:34 +0000
Message-ID: 
 <PH3PPF1C905D6E61EEB89CF46C1F572D402E16EA@PH3PPF1C905D6E6.namprd15.prod.outlook.com>
References: 
 <PH3PPF1C905D6E6F24A5C1A1A1D8345B593E16FA@PH3PPF1C905D6E6.namprd15.prod.outlook.com>
 <3e539c8b-c95a-4ba9-8462-04045b2da2b0@dalibo.com>
In-Reply-To: <3e539c8b-c95a-4ba9-8462-04045b2da2b0@dalibo.com>
Accept-Language: en-GB, en-US
Content-Language: en-GB
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?Windows-1252?Q?TIbiSi6knyUcnY6W6OOumBfyPYmBlcry1aRCx0hg32aEhD0wvKEbmvc0?=
 =?Windows-1252?Q?Kcg3Ift2oZIv0hdzG5+vHIi2FQqMPp2Je0oF92xWOQ+9miVpv2we5N+g?=
 =?Windows-1252?Q?54ltdrSM+UdfoKI+Mrb8Ljhn1jf7/t1+dsEGrBCuQqhoeSRiVUwxbzft?=
 =?Windows-1252?Q?tU76qD38ajTZuq84ZApbXZ9Nc8parkRIudkZBCbxJca4PtDNqM6v1Jq2?=
 =?Windows-1252?Q?q0lHAdOs3++S1G4vdwP0n9WufDKxeOwfOxGk+M/8uRNvBkO4b1WB5O7O?=
 =?Windows-1252?Q?nI1wg/GAGVqJpGe/be4lS/moxotFm/aa5HxhQU9Cy64I/UFWZMBGtOHO?=
 =?Windows-1252?Q?5Sq6I0X7uL4aTtORQzSZchh5/3pOZQuDP8XWFCpl7dtLX1vBHL21TSiI?=
 =?Windows-1252?Q?a51wPy7P+uljweJxbK+I1bQyxbmRFRrCfB9ft625PnkX99rLVEiuJlgm?=
 =?Windows-1252?Q?CXDW8ZXgq/kaIiwJPQj+icwekORcCUusM8yT1Q9+yMc+GbUhV2Qd5LIw?=
 =?Windows-1252?Q?JLw0UQCxDiHmn4Dy4N2plBctzrDML4amKEGR9T8hqmUHfmbqV4rzaEWr?=
 =?Windows-1252?Q?0xtJWnqk3+ISeykmY3Hlbh304l0cteRUXDi54bBaXwOz8vFq2Q8ahCUA?=
 =?Windows-1252?Q?YGMV7IkrLo3jRD3xZheLaWabjyVMt3refVSRrEGZIkcAc5Gnz7749fjZ?=
 =?Windows-1252?Q?DX+rZRWO1Kf8dvS3+g+0MOCozbSjkg1/t7a+msg/xWGSlWFO0zskfkr+?=
 =?Windows-1252?Q?eGcQM/JxulVDhtnNYGDNndTC8xxJbSRdBQCUC+37FZgVAyNyZR23iWn2?=
 =?Windows-1252?Q?tPQ/lm58KfJrnMxLaddS5JfBIyevRZsj6XLsQdkeVdRjVZB9+mDcxu0v?=
 =?Windows-1252?Q?GxiBwR/i5NtKeM5GG2ImiTLop2qImIIxlkfTlWoDTxosIMsw6eECMg4M?=
 =?Windows-1252?Q?2J25P95qmqPIguqRooxXcG6eJ5j83m/J/S54FO3u8iNvceklM4kG4Y3a?=
 =?Windows-1252?Q?QpnwwpjlglpHpWbB2X1lJxwo2JyToifPfL23N/mWOhP1VE3MeB5IEs3b?=
 =?Windows-1252?Q?gpzfyOCNYQ4mUWwPQexrlxmIM38MOBhcoeADRgiJYOyEqpfmgk/T4Slp?=
 =?Windows-1252?Q?aXvZXlmaWb+So4edqvy/T+BGcme6cJ08O+Ny90gkmaMJ+B2Sw0qRwg9L?=
 =?Windows-1252?Q?/Tr7iCUs8cEzqeviIbH1RsXg8UJA/TeXo+g96s00EIfinybFw96QacGu?=
 =?Windows-1252?Q?FPdaQDoquhtcPbMW9V57OGGYh42RKES4ProHxJSY0gag0tAIXsp/gFoP?=
 =?Windows-1252?Q?osp5orfIX1xTal9oqUyjQugwydcGUAB2ejDg/jAUTLNJ372XcBNi2Zdp?=
 =?Windows-1252?Q?bpzFY9+SLgoRaGewhX+Ox+Z8ljvQgqnKGVZ5pGC/LvmCNptzcsbL3Ioq?=
 =?Windows-1252?Q?kWkMBus2nJu7LWqLQDF4ZgyvBL5K1PAEsN0Cp3uhK0tXTw8qfbPCCYYC?=
 =?Windows-1252?Q?ZLjymPVfhZDp8HQopAUhwNGlKq2+QdEUF4A34jS3wPpAgnNfYZBo5Wxk?=
 =?Windows-1252?Q?sfe1fO20tZ7hY7Duszd3MkGSEo2tX3ug7vcJc+RWH124jan5TX3+qVky?=
 =?Windows-1252?Q?lvpM5HBWAMkKyLN2ErKBrEPUUGoHVCqktUlx6Z8LyKJ9iO6DMb2D/vtP?=
 =?Windows-1252?Q?LAdQ49R3X3turtleDlpAWorlpt8vbzXtk5XmjaIxoA62lsE0uvG64g?=
 =?Windows-1252?Q?=3D=3D?=
Content-Type: multipart/alternative;
	boundary="_000_PH3PPF1C905D6E61EEB89CF46C1F572D402E16EAPH3PPF1C905D6E6_"
MIME-Version: 1.0
X-OriginatorOrg: uk.ibm.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: 
 PH3PPF1C905D6E6.namprd15.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 28a94b34-8a4e-4107-365b-08dda4db883a
X-MS-Exchange-CrossTenant-originalarrivaltime: 06 Jun 2025 09:21:34.6324
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: fcf67057-50c9-4ad4-98f3-ffca64add9e9
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 Cnbf6fpR6QNQ+zPS0J4zjz0kF4X/HZ33pIyoa7Onua5b9L/XNiu0ZIy4Vi2gy0whybIsXYcPY9rZN6O6UxUkwg==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CH4PR15MB6698
X-Proofpoint-GUID: 3gwCMJInnwaBJsKaVG0ZXwGYQx_ez_gV
X-Proofpoint-ORIG-GUID: 3gwCMJInnwaBJsKaVG0ZXwGYQx_ez_gV
X-Authority-Analysis: v=2.4 cv=SO9CVPvH c=1 sm=1 tr=0 ts=6842b321 cx=c_pps
 a=5IBKRMRyaRRtUKQpU9qwqQ==:117 a=lCpzRmAYbLLaTzLvsPZ7Mbvzbb8=:19
 a=wKuvFiaSGQ0qltdbU6+NXLB8nM8=:19 a=Ol13hO9ccFRV9qXi2t6ftBPywas=:19
 a=xqWC_Br6kY4A:10 a=6IFa9wvqVegA:10
 a=IUXmofTVTC5u9MR0iaEA:9 a=pILNOxqGKmIA:10 a=yMhMjlubAAAA:8 a=SSmOFEACAAAA:8
 a=u6SR_h4kbJ2TLSpBY38A:9 a=of6HvCouH_Iq9vPR:21 a=gKO2Hq4RSVkA:10
 a=UiCQ7L4-1S4A:10 a=hTZeC7Yk6K0A:10 a=frz4AuCg-hUA:10
X-Proofpoint-Spam-Details-Enc: AW1haW4tMjUwNjA2MDA4MiBTYWx0ZWRfX3RNyZFhAOnut
 +/+AEwAvG75UcYIx4jJfocsSg2/Gli+QT3mMfd1pMSvl96rgwYAdpZ2U/BEdTY+mfBhXLtI5sy4
 bXGDmu34HoUK3Y1SIy9jOAIytyzldzY+y3ZkBelObybP3nuhB3mWiA7UDQ//SlJXO8ffLOEHIpi
 VvZHZviqDxTpzCbAA2FQwLGIrlD5VsFi1qLv/F39xfPFIwQmn8rfLfndIIjaDlefs+3WCxmYOs7
 8mZVzqWWUOlc/dyfH8IrDikRLPFgyGVHBVLQSWsh8fnafvlxhCg/j8JcGkF+TG/sIKk2HpprYFz
 fwRvZXbOxoBNMWrVcTdylmUZ3NuWwANXHt9CTT8J5n69x6xkvelX7cQZb88yI5ssGFipEd9vOgv
 /cc2XqTKBMcztsGyzmY/8PPDUCLlvtBfvaXFKU/hSPYoDnPe+n+UiYaEaF5ZHgfLV9GXKVok
Subject: RE: Poor row estimates from planner,
 stat `most_common_elems` sometimes
 missing for a text[] column
X-Proofpoint-Virus-Version: vendor=baseguard
 engine=ICAP:2.0.293,Aquarius:18.0.1099,Hydra:6.0.736,FMLib:17.12.80.40
 definitions=2025-06-06_02,2025-06-05_01,2025-03-28_01
X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0
 malwarescore=0
 impostorscore=0 phishscore=0 priorityscore=1501 adultscore=0
 mlxlogscore=999 clxscore=1015 spamscore=0 lowpriorityscore=0 bulkscore=0
 mlxscore=0 suspectscore=0 classifier=spam authscore=0 authtc=n/a authcc=
 route=outbound adjust=0 reason=mlx scancount=1 engine=8.19.0-2505280000
 definitions=main-2506060082
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Archived-At: 
 <https://www.postgresql.org/message-id/PH3PPF1C905D6E61EEB89CF46C1F572D402E16EA%40PH3PPF1C905D6E6.namprd15.prod.outlook.com>
Precedence: bulk

--_000_PH3PPF1C905D6E61EEB89CF46C1F572D402E16EAPH3PPF1C905D6E6_
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="windows-1252"


> On 6/5/25 17:42, Mark Frost wrote:
> > Is there any good explanation for this behaviour? Preferably we=92d like
> > some way for proper `most_common_elems` statistics to be collected in
> > our production database, in the hope that influences a good query plan
> > to always be selected.


> most_common_elems has a limited size, and if all the elements have the
> same freq, there's nothing we can do.

>  You could do: alter table test alter column tags set statistics X;
>  However, X is capped at 10000=85

Actually *any* most_common_elems stats would be fine, because the reasoning=
 is:

  *   If the searched element is in most_common_elems we know it=92s freque=
ncy
  *   If it=92s not, it=92s less frequent than the least most_common_elems

So in our case when every row is unique, we=92d only actually need stats to=
 record a single most_common_elems (if only it would record one)

Unless otherwise stated above:

IBM United Kingdom Limited
Registered in England and Wales with number 741598
Registered office: Building C, IBM Hursley Office, Hursley Park Road, Winch=
ester, Hampshire SO21 2JN

--_000_PH3PPF1C905D6E61EEB89CF46C1F572D402E16EAPH3PPF1C905D6E6_
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset="windows-1252"

<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" xmlns:w=3D"urn:sc=
hemas-microsoft-com:office:word" xmlns:m=3D"http://schemas.microsoft.com/of=
fice/2004/12/omml" xmlns=3D"http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DWindows-1=
252">
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:Wingdings;
	panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Aptos;
	panose-1:2 11 0 4 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	font-size:10.0pt;
	font-family:"Aptos",sans-serif;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
	{mso-style-priority:34;
	margin-top:0cm;
	margin-right:0cm;
	margin-bottom:0cm;
	margin-left:36.0pt;
	font-size:10.0pt;
	font-family:"Aptos",sans-serif;}
span.EmailStyle19
	{mso-style-type:personal-reply;
	font-family:"Aptos",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-size:10.0pt;
	mso-ligatures:none;}
@page WordSection1
	{size:612.0pt 792.0pt;
	margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
	{page:WordSection1;}
/* List Definitions */
@list l0
	{mso-list-id:1601987243;
	mso-list-type:hybrid;
	mso-list-template-ids:-424796260 -1379907088 134807555 134807557 134807553=
 134807555 134807557 134807553 134807555 134807557;}
@list l0:level1
	{mso-level-start-at:0;
	mso-level-number-format:bullet;
	mso-level-text:-;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:"Aptos",sans-serif;
	mso-fareast-font-family:Aptos;
	mso-bidi-font-family:"Times New Roman";}
@list l0:level2
	{mso-level-number-format:bullet;
	mso-level-text:o;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:"Courier New";}
@list l0:level3
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7 ;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:Wingdings;}
@list l0:level4
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7 ;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:Symbol;}
@list l0:level5
	{mso-level-number-format:bullet;
	mso-level-text:o;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:"Courier New";}
@list l0:level6
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7 ;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:Wingdings;}
@list l0:level7
	{mso-level-number-format:bullet;
	mso-level-text:\F0B7 ;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:Symbol;}
@list l0:level8
	{mso-level-number-format:bullet;
	mso-level-text:o;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:"Courier New";}
@list l0:level9
	{mso-level-number-format:bullet;
	mso-level-text:\F0A7 ;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-18.0pt;
	font-family:Wingdings;}
ol
	{margin-bottom:0cm;}
ul
	{margin-bottom:0cm;}
--></style>
</head>
<body lang=3D"EN-GB" link=3D"#467886" vlink=3D"#96607D" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<div id=3D"mail-editor-reference-message-container">
<div>
<div>
<div>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt"><br>
&gt; On 6/5/25 17:42, Mark Frost wrote:<br>
&gt; &gt; Is there any good explanation for this behaviour? Preferably we=
=92d like <br>
&gt; &gt; some way for proper `most_common_elems` statistics to be collecte=
d in <br>
&gt; &gt; our production database, in the hope that influences a good query=
 plan <br>
&gt; &gt; to always be selected.<br>
<br>
<br>
&gt; most_common_elems has a limited size, and if all the elements have the=
 <o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt">&gt; same freq, the=
re's nothing we can do.<br>
<br>
&gt; &nbsp;You could do: alter table test alter column tags set statistics =
X;<br>
&gt; &nbsp;However, X is capped at 10000=85<br>
<br>
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt">Actually *<b>any</b=
>* </span>
<span style=3D"font-size:11.0pt">most_common_elems</span><span style=3D"fon=
t-size:11.0pt"> stats would be fine, because the reasoning is:<o:p></o:p></=
span></p>
<ul style=3D"margin-top:0cm" type=3D"disc">
<li class=3D"MsoListParagraph" style=3D"margin-left:0cm;mso-list:l0 level1 =
lfo1"><span style=3D"font-size:11.0pt">If the searched element is in
</span><span style=3D"font-size:11.0pt">most_common_elems we know it=92s fr=
equency</span><span style=3D"font-size:11.0pt"><o:p></o:p></span></li><li c=
lass=3D"MsoListParagraph" style=3D"margin-left:0cm;mso-list:l0 level1 lfo1"=
><span style=3D"font-size:11.0pt">If it=92s not, it=92s less frequent than =
the least most_common_elems</span><span style=3D"font-size:11.0pt"><o:p></o=
:p></span></li></ul>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt"><o:p>&nbsp;</o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt">So in our case when=
 every row is unique, we=92d only actually need stats to record a single
</span><span style=3D"font-size:11.0pt">most_common_elems (if only it would=
 record one)</span><span style=3D"font-size:11.0pt"><o:p></o:p></span></p>
</div>
</div>
</div>
</div>
</div>

<DIV>
Unless otherwise stated above:<BR>
<BR>
IBM United Kingdom Limited<BR>
Registered in England and Wales with number 741598<BR>
Registered office: Building C, IBM Hursley Office, Hursley Park Road, Winch=
ester, Hampshire SO21 2JN<BR>
</DIV></body>
</html>

--_000_PH3PPF1C905D6E61EEB89CF46C1F572D402E16EAPH3PPF1C905D6E6_--