From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
cc: Paul A Jungwirth <pj@illuminatedcomputing.com>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Improve rowcount estimate for UNNEST(column)
In-reply-to: <bdaf96bfbdd9a8a32bba43f91d1ac6dcfd237d35.camel@cybertec.at>
References: <CA+renyUnM2d+SmrxKpDuAdpiq6FOM=FByvi6aS6yi__qyf6j9A@mail.gmail.com> <bdaf96bfbdd9a8a32bba43f91d1ac6dcfd237d35.camel@cybertec.at>
Comments: In-reply-to Laurenz Albe <laurenz.albe@cybertec.at>
	message dated "Sun, 26 Nov 2023 21:11:59 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3699082.1701030124.1@sss.pgh.pa.us>
Date: Sun, 26 Nov 2023 15:22:04 -0500
Message-ID: <3699083.1701030124@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/3699083.1701030124%40sss.pgh.pa.us>
Precedence: bulk

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Sat, 2023-11-25 at 09:19 -0800, Paul A Jungwirth wrote:
>> Here is a patch to improve rowcount estimates for
>> `UNNEST(some_array_column)`. Today we hard code this to 10, but we
>> have statistics about array size, so it's easy to use them.

> The idea sounds good to me.

I didn't read the patch either yet, but it seems like a reasonable idea.

> I didn't test or scrutinize the code, but I noticed that you use
> EXPLAIN in the regression tests.  I think that makes the tests vulnerable
> to changes in the parameters or in the block size.

Yes, this regression test is entirely unacceptable; the numbers will
not be stable enough.  Even aside from the different-settings issue,
you can't rely on ANALYZE deriving exactly the same stats every time.
Usually what we try to do is devise a query where the plan shape
changes because of the better estimate.  That typically will provide
some insulation against small changes in the numerical estimates.

			regards, tom lane