public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Rowley <[email protected]>
To: Sašo Gantar <[email protected]>
Cc: [email protected]
Subject: Re: problem with query
Date: Tue, 21 May 2024 22:48:48 +1200
Message-ID: <CAApHDvqUFqHNmfo+9KcF_JJ6uKqRPF1LfqXrqbcCfGPqpE9UGA@mail.gmail.com> (raw)
In-Reply-To: <CAGB0_640Kv4c9Kq5qDNZCNcAxo-_aO=AaKJqBann+iNiU0imWA@mail.gmail.com>
References: <CAGB0_6600w5C=hvhgfMWCqO9BCwCg+3s0PxXuoQv48NLqTp6dA@mail.gmail.com>
<CAApHDvq=sj+vVQ6HKOdNLeWbQnHErg5QPSz26s0a90PtY1NLdw@mail.gmail.com>
<CAGB0_640Kv4c9Kq5qDNZCNcAxo-_aO=AaKJqBann+iNiU0imWA@mail.gmail.com>
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
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: problem with query
In-Reply-To: <CAApHDvqUFqHNmfo+9KcF_JJ6uKqRPF1LfqXrqbcCfGPqpE9UGA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox