Re: problem with query

On Mon, 20 May 2024 at 23:09, Sašo Gantar <sasog23(at)gmail(dot)com> wrote:
> what helps is
> SET enable_nestloop = off;
> query takes less then 2seconds
> but it's probably not a good idea to change this flag

Looks like it's slow due to a bad selectivity estimate on the join
between pgn and pgc. This results in:

-> Nested Loop (cost=39.47..80.56 rows=1 width=133) (actual
time=0.179..0.475 rows=57 loops=1)

because the row estimate is 1, from there down to the root of the plan
tree the planner thinks Nested Loop is a good join type for the
remaining joins. Unfortunately, it's wrong.

I don't really see a good way to convince the planner not to do this.
The problem condition is:

Recheck Cond: (relnamespace = pgn.oid)
Filter: (relkind = ANY ('{r,v,f,m,p}'::"char"[]))

if ANALYZE pg_class; does not help then you could maybe mess with the
n_distinct estimate on pg_class.relnamespace, but you risk making
other queries worse.

Disabling enable_nestloop might be a good option, if you can just do
it for this query. Unfortunately, parameterized nested loops are also
tied into that GUC, so you'll stop those working for this plan. The
Nested Loop between pgn and pgc looks like a good choice. The rest,
not so much.

I don't think (ndistinct) extended statistics on pg_class
relnamespace, relkind will help since "relnamespace = pgn.oid" is a
join condition.

David