Re: using extended statistics to improve join estimates

On 11/22/21 02:23, Justin Pryzby wrote:
> Your regression tests include two errors, which appear to be accidental, and
> fixing the error shows that this case is being estimated poorly.
>
> +-- try combining with single-column (and single-expression) statistics
> +DROP STATISTICS join_test_2;
> +ERROR: statistics object "join_test_2" does not exist
> ...
> +ERROR: statistics object "join_stats_2" already exists
>

D'oh, what a silly mistake ...

You're right fixing the DROP STATISTICS results in worse estimate, but
that's actually expected for a fairly simple reason. The join condition
has expressions on both sides, and dropping the statistics means we
don't have any MCV for the join_test_2 side. So the optimizer ends up
not using the regular estimates, as if there were no extended stats.

A couple lines later the script creates an extended statistics on that
expression alone, which fixes this. An expression index would do the
trick too.

Attached is a patch fixing the test and also the issue reported by
Zhihong Yu some time ago.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company