Subject: Re: problem with query

From: Sašo Gantar <[email-masked]>
Date: Mon, 20 May 2024 13:09:00 +0200

what helps is
SET enable_nestloop = off;
query takes less then 2seconds

but it's probably not a good idea to change this flag

On Wed, 15 May 2024 at 13:23, David Rowley <[email-masked]> wrote:

> On Wed, 15 May 2024 at 21:08, Sašo Gantar <[email-masked]> wrote:
> > this query takes more than 8 seconds,
> > if i remove "AND ((pgn.nspname='servicedesk'))" and test it, it takes <1s
>
> Including the EXPLAIN rather than EXPLAIN (ANALYZE, BUFFERS) isn't
> very useful as there's no way to tell if the planner's estimates were
> accurate or not. Also with EXPLAIN only, we don't know where the time
> was spent in the query.
>
> Running the EXPLAIN with "SET track_io_timing = 1;" would be even more
> useful.
>
> David
>

---

From: David Rowley <[email-masked]>
Date: Tue, 21 May 2024 22:48:48 +1200

> 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