From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Frost <FROSTMAR@uk.ibm.com>
cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>,
        Alexander Korotkov <aekorotkov@gmail.com>
Subject: Re: Poor row estimates from planner,
 stat `most_common_elems` sometimes missing for a text[] column
In-reply-to: <1311236.1749149830@sss.pgh.pa.us>
References: 
 <PH3PPF1C905D6E6F24A5C1A1A1D8345B593E16FA@PH3PPF1C905D6E6.namprd15.prod.outlook.com>
 <1311236.1749149830@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Thu, 05 Jun 2025 14:57:10 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1331732.1749160373.1@sss.pgh.pa.us>
Date: Thu, 05 Jun 2025 17:52:53 -0400
Message-ID: <1331733.1749160373@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1331733.1749160373%40sss.pgh.pa.us>
Precedence: bulk

I wrote:
> The part of that that seems to be going off the rails is
> this selection of a cutoff frequency below which element values
> will be dropped:

>         cutoff_freq = 9 * element_no / bucket_width;

> The first thing I find suspicious here is that the calculation is
> based on element_no (the total number of array elements processed)
> and not nonnull_cnt (the maximum possible frequency).  Is that
> really right?

I did some more digging and found that that calculation was introduced
(in the older tsvector code) in bc0f08092, which traces to this
discussion:

https://www.postgresql.org/message-id/flat/4BF4357E.6000505%40krogh.cc

So the use of element_no is correct, because what we need to consider
here is the total number of values fed to the LC algorithm.

Also, my thought that maybe we should reject entries with f < 2
is bogus, because at the end of the algorithm f is not necessarily
the true count of occurrences of the value: some early occurrences
could have been forgotten via pruning.  The "behavioral cliff" is
annoying but I'm not sure there is much to be done about it: having
a single (still-remembered) occurrence gets less and less significant
as the total input size increases, so sooner or later you are going
to hit a point where such values should be thrown away.

So at this point I'm thinking that there is nothing wrong with
ANALYZE's algorithm, although I now see that there are some relevant
comments in ts_typanalyze.c that probably ought to be transposed into
array_typanalyze.c.

The idea of treating lack of MCELEM differently from complete
lack of stats still seems to have merit, though.

			regards, tom lane