From: Justin Pryzby <pryzby@telsasoft.com>
To: Simen Andreas Andreassen Lønsethagen <simen.lonsethagen@fremtind.no>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Performance of lateral join
Date: Tue, 27 Jul 2021 21:27:33 -0500
Message-ID: <20210728022733.GM23997@telsasoft.com> (raw)
In-Reply-To: <58EEA217-E918-49C0-987A-E634AD6BB1B2@fremtind.no>
References: <A67CAB4B-C442-4641-BDF1-EDBE1DBF5876@fremtind.no>
<20210726181745.GE23997@telsasoft.com>
<58EEA217-E918-49C0-987A-E634AD6BB1B2@fremtind.no>
On Tue, Jul 27, 2021 at 09:08:49AM +0000, Simen Andreas Andreassen Lønsethagen wrote:
> > Easy first question: is the temp table analyzed before being used in a join ?
>
> No, I haven't done that. Today, I tried to run
>
> ANALYZE records_to_filter_on;
>
> on the same sample data set (3.75 million rows) before the join, and it did not seem to make much of a difference in terms of time (new output of EXPLAIN ANALYZE at https://explain.dalibo.com/plan/YZu - it seems very similar to me).
If the "shape" of the plan didn't change, then ANALYZE had no effect.
I think you'd see an improvement if both tables were ordered by foedselsnummer.
It might be that that's already somewhat/partially true (?)
I suggest to create an index on the temp table's r.foedselsnummer, CLUSTER on
that index, and then ANALYZE the table. The index won't be useful for this
query, it's just for clustering (unless you can instead populate the temp table
in order).
Check if there's already high correlation of dpd_bis_foedselsnummer (over 0.9):
| SELECT tablename, attname, inherited, null_frac, n_distinct, correlation FROM pg_stats WHERE attname='dpd_bis_foedselsnummer' AND tablename='...';
If not, consider clustering on the existing "unique_descending" index and then
analyzing that table, too.
This would also affect performance of other queries - hopefully improving
several things at once.
--
Justin
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: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, simen.lonsethagen@fremtind.no, pgsql-performance@lists.postgresql.org
Subject: Re: Performance of lateral join
In-Reply-To: <20210728022733.GM23997@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox