Re: Chaotic query planning ?

Philippe Ivaldi wrote:
> The explain analyze of the following code is https://explain.depesz.com/s/VhOv
>
> [OTHER CTEs - TRUNCATED CODE]
> SELECT
> count(*)
> FROM dossier d
> LEFT JOIN vp ON vp.dossier_id = d.id
> LEFT JOIN affected_ccial ON affected_ccial.dossier_id = d.id
> LEFT JOIN dm_bien ON dm_bien.dossier_id = d.id
> LEFT JOIN rdv_r2 ON rdv_r2.dossier_id = d.id
> LEFT JOIN rdv_ra ON rdv_ra.dossier_id = d.id
> LEFT JOIN mandat_papier_non_recu ON mandat_papier_non_recu.dossier_id = d.id
> LEFT JOIN annonce csite_annonce_enabled ON csite_annonce_enabled.dossier_id = d.id
> LEFT JOIN invalidated_estimation ON invalidated_estimation.dossier_id = d.id
> LEFT JOIN num_mandat_reserved ON num_mandat_reserved.dossier_id = d.id
> LEFT JOIN d_status ON d_status.dossier_id = d.id
> WHERE [...]
>
> [...]
>
> If I permute the line
> LEFT JOIN vp ON vp.dossier_id = d.id
> with
> LEFT JOIN affected_ccial ON affected_ccial.dossier_id = d.id
>
> The explain analyze is https://explain.depesz.com/s/sKGW
> resulting in a total time of 798.693ms instead of 65,843.533ms
>
> 1. Can somebody explain me why the second query is near 100 faster than the
> first one ?
>
> 2. Is there a rule that suggest the best order of the statements JOIN ?
> I'd read this doc https://www.postgresql.org/docs/9.6/static/explicit-joins.html
> but I don't see any logic join order in this case…
>
> 3. Why the two queries are very fast when I remove the WHERE
> conditions ?
>
> I can provide additional informations if needed.

You join more than 8 tables in your query, and 8 is the default
value for join_collapse_limit.

https://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-JOIN-COLLAPSE-LIMIT

In this case, PostgreSQL doesn't perform an exhaustive search of
the possible query plans, but joins them in the order provided.

Experiment with raising join_collapse_limit and from_collapse_limit to 11.

Alternatively, optimize the join order by hand and don't tune the parameters.

Yours,
Laurenz Albe