Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 11.5 \(3445.9.7\))
Subject: Re: Major performance degradation with joins in 15.8 or 15.7?
From: Ed Sabol <edwardjsabol@gmail.com>
In-Reply-To: <2afa93b9-17d9-4bdd-bee2-f33dc12383cc@gmail.com>
Date: Thu, 7 Nov 2024 21:45:37 -0500
Cc: pgsql-performance@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <2F4F210E-3BEA-4365-AB19-AC7917EF9F49@gmail.com>
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>
To: Andrei Lepikhov <lepihov@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/2F4F210E-3BEA-4365-AB19-AC7917EF9F49%40gmail.com>
Precedence: bulk

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 and shifted the =
decision to use materialisation.

Interesting, except I decreased the random_page_cost. Just clarifying.

> I see huge underestimation in the simple scan:
>=20
> Bitmap Heap Scan on metainfo b_1
>  (cost=3D23.96..35.77 rows=3D3 width=3D38)
>  (actual time=3D1.225..4.206 rows=3D1025 loops=3D1)
>=20
> It may be caused by some functional dependency in its filter:
>=20
> ((relation =3D 'located'::text) AND (type =3D 'document'::text))
>=20
> You can create extended statistics on the columns 'relation' and =
'type'. These statistics can reduce estimation errors and enable the =
optimiser to find a better plan without changing the cost balance.

OK, this is the first I'm learning about extended statistics...

I'm looking at =
https://www.postgresql.org/docs/15/sql-createstatistics.html
and =
https://www.postgresql.org/docs/15/planner-stats.html#PLANNER-STATS-EXTEND=
ED

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;

Thanks,
Ed