Message-ID: <b98a67c1-93db-40d7-a3bb-61ec51a80f55@gmail.com>
Date: Fri, 8 Nov 2024 09:54:10 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Major performance degradation with joins in 15.8 or 15.7?
To: Ed Sabol <edwardjsabol@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
References: <197E1EF0-BEDF-42C0-8508-56BC6E88AA35@gmail.com>
 <CAApHDvr8c4-unAVysqMgz0mWO=PZ_WAGprq-jwND5+C_OO8SYg@mail.gmail.com>
 <7A0AB841-65F3-4317-905C-E98D45953E3E@gmail.com>
 <2afa93b9-17d9-4bdd-bee2-f33dc12383cc@gmail.com>
 <2F4F210E-3BEA-4365-AB19-AC7917EF9F49@gmail.com>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <2F4F210E-3BEA-4365-AB19-AC7917EF9F49@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/b98a67c1-93db-40d7-a3bb-61ec51a80f55%40gmail.com>
Precedence: bulk

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 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