Re: [PERFORM] 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