On Sat, 13 Jun 2020 at 06:26, regrog <andrea.vencato@gmail.com> wrote:
>
> I'm facing performance issues migrating from postgres 10 to 12 (also from 11
> to 12) even with a new DB.
> Th performance difference is huge 300ms in pg10 vs 3 minutes in pg12.
This appears to be down to bad statistics that cause pg12 to choose a
nested loop plan. The pg12 plan has:
-> Hash Join (cost=1281.91..2934.18 rows=68 width=192) (actual
time=0.024..21.915 rows=3538 loops=1848)"
on the inner side of a nested loop. 21.915 * 1848 loops is 40498.92
ms, so most of the time.
This comes down to the difference caused by 04fe805a17, where after
that commit we don't bother looking at the NOT NULL constraints in
table_constraints.
explain select * from (select * from
information_schema.table_constraints) c where constraint_type <>
'CHECK';
If you execute the above on both instances, you'll see PG12 does not
do an Append. PG10 does. Which results in more rows being estimated
and the planner choosing something better than a nested loop join.
You could try: SET enable_nestloop TO off;
I'm not really sure there's much you could do to improve the
statistics on the catalogue tables.
Alternatively, you could write a view based directly on the base
tables, bypassing information_schema completely.
David