On 11/8/24 09:45, Ed Sabol wrote:
> On Nov 7, 2024, at 9:27 PM, Andrei Lepikhov <lepihov@gmail.com> wrote:
>> Postgres didn't want Materialize in this example because of the low estimation on its outer subquery. AFAIC, by
increasingthe *_page_cost's value, you added extra weight to the inner subquery
> What kind of extended statistics do you suggest for this? ndistinct, dependencies, or mcv?
>
> CREATE STATISTICS tablename_stats (<statistics type?>) ON relation, type FROM tablename;
> ANALYZE tablename;
I'd recommend to use all of them - MCV is helpful in most of the cases
(and relatively cheap), distinct is actually used in Postgres now to
calculate number of groups (GROUP-BY, Sort, Memoize, etc.); dependencies
- to find correlations between columns - usually in scan filters.
--
regards, Andrei Lepikhov
