Re: Performance of lateral join

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