From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> |
Cc: | jian he <jian(dot)universality(at)gmail(dot)com>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Improve rowcount estimate for UNNEST(column) |
Date: | 2024-01-04 23:45:07 |
Message-ID: | 841185.1704411907@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> writes:
> Here is a patch with an improved test. With the old "10" estimate we get a Merge Join, but now that
> the planner can see there are only ~4 elements per array, we get a Nested Loop.
Pushed with minor editorialization. I ended up not using the test
case, because I was afraid it wouldn't be all that stable, and
code coverage shows that we are exercising the added code path
even without a bespoke test case.
> On 11/29/23 20:35, jian he wrote:
>>> I did a minor change. change estimate_array_length return type to double
> I'm not sure I want to change estimate_array_length from returning
> ints to returning doubles, since it's called in many places.
But your patch forces every one of those places to be touched anyway,
as a consequence of adding the "root" argument. I looked at the
callers and saw that every single one of them (in core anyway) ends up
using the result in a "double" rowcount calculation, so we're really
not buying anything by converting to integer and back again. There's
also a question of whether the number from DECHIST could be big enough
to overflow an int. Perhaps not given the current calculation method,
but since it's a float4 there's at least a theoretical risk. Hence,
I adopted Jian's suggestion.
One other point is that examine_variable is capable of succeeding
on things that aren't Vars, so I thought the restriction to Vars
was inappropriate.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2024-01-04 23:48:33 | Re: Add a perl function in Cluster.pm to generate WAL |
Previous Message | Michael Paquier | 2024-01-04 23:38:22 | Re: Adding facility for injection points (or probe points?) for more advanced tests |