On 11/8/24 09:45, Ed Sabol wrote:
On Nov 7, 2024, at 9:27 PM, Andrei Lepikhov <lepihov@xxxxxxxxx> wrote:
Postgres didn't want Materialize in this example because of the low estimation on its outer subquery. AFAIC, by increasing the *_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
