public inbox for [email protected]
help / color / mirror / Atom feedRe: problem with query
2+ messages / 2 participants
[nested] [flat]
* Re: problem with query
@ 2024-05-20 11:09 Sašo Gantar <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Sašo Gantar @ 2024-05-20 11:09 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: [email protected]
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 protected]> wrote:
> On Wed, 15 May 2024 at 21:08, Sašo Gantar <[email protected]> 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
>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: problem with query
@ 2024-05-21 10:48 David Rowley <[email protected]>
parent: Sašo Gantar <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: David Rowley @ 2024-05-21 10:48 UTC (permalink / raw)
To: Sašo Gantar <[email protected]>; +Cc: [email protected]
On Mon, 20 May 2024 at 23:09, Sašo Gantar <[email protected]> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-05-21 10:48 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-20 11:09 Re: problem with query Sašo Gantar <[email protected]>
2024-05-21 10:48 ` David Rowley <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox