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: 
 <PH3PPF1C905D6E6F24A5C1A1A1D8345B593E16FA@PH3PPF1C905D6E6.namprd15.prod.outlook.com>
References: 
 <PH3PPF1C905D6E6F24A5C1A1A1D8345B593E16FA@PH3PPF1C905D6E6.namprd15.prod.outlook.com>
Comments: In-reply-to Mark Frost <FROSTMAR@uk.ibm.com>
	message dated "Thu, 05 Jun 2025 15:42:00 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1311235.1749149830.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 05 Jun 2025 14:57:10 -0400
Message-ID: <1311236.1749149830@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1311236.1749149830%40sss.pgh.pa.us>
Precedence: bulk

Mark Frost <FROSTMAR@uk.ibm.com> writes:
> We're seeing intermittently very poor performance of a query, when occas=
ionally a poor query plan is chosen. We're using Postgres 16.9.
> One suspicious factor when looking at the EXPLAIN ANALYZE output, is a v=
ery wrong estimated number of rows to be returned from a text[] column que=
ried with '&&'.
> After playing around with a simple recreate (details below), it seems AN=
ALYZE of the table is affected by the number of rows in the table. Statist=
ic `most_common_elems` is [null] when there's over 15,873 rows in the tabl=
e when analyzed. With fewer rows it's analyzed correctly.

Thanks for the report.  Poking through the code, it seems like
there are two distinct problems here:

1. ANALYZE uses a "lossy counting" algorithm (dating to commit
0e5e167aa) to estimate the frequencies of array element values.
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:

        /*
         * Construct an array of the interesting hashtable items, that is,
         * those meeting the cutoff frequency (s - epsilon)*N.  Also ident=
ify
         * the minimum and maximum frequencies among these items.
         *
         * Since epsilon =3D s/10 and bucket_width =3D 1/epsilon, the cuto=
ff
         * frequency is 9*N / bucket_width.
         */
        cutoff_freq =3D 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?  It wouldn't change the results in your reproducer
with just one element per array, but it seems bogus.

More relevant to your immediate problem, this creates a behavioral
cliff at the point where cutoff_freq rises from 0 to 1, which with the
default attstattarget turns out to be, you guessed it, 15873 elements.
In your example, all the element values have frequency 1, so that
switches us from being willing to record all the values to being
willing to record none of them.  That doesn't feel right either.
By analogy to our treatment of regular MCVs, it seems like we should
never be willing to store values that we didn't see at least twice.
Of course, doing that would make this example worse, because then
we'd not store any "most common" elements at smaller rowcounts either.
Which brings us to the other major problem this reveals:

2. In array_selfuncs.c, we fall back to default selectivity
estimates if there's no MCELEM statistics field.  What we
should be doing, if there are other stats for the column but
not MCELEM, is realizing that compute_array_stats did not find
any elements that are common enough to be worth recording.
Then we'd use some much lower-than-default estimate for the
selectivity.

I don't have any immediate patch to offer, but clearly this
area could use another look.

compute_array_stats seems to have borrowed the lossy-counting
algorithm from ts_typanalyze, so we'd better take a look at
that too.

			regards, tom lane