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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
