Chaotic query planning ?

Hi,

I'm new in this mailing list, sorry if my post is not well formed.

First of all, I would thank all the team and the contributors
around PostgreSQL for their work.

My question…

The explain analyze of the following code is https://explain.depesz.com/s/VhOv
✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯
WITH vp AS (
SELECT dossier.id AS dossier_id
FROM dossier, vente_process
WHERE dossier.id = vente_process.dossier_id
GROUP BY dossier.id
)
, affected_ccial AS (
SELECT
d.id AS dossier_id
FROM dossier d, dossier_rel_ccial
WHERE date_stop > now()
AND dossier_rel_ccial.enabled
AND d.id = dossier_rel_ccial.dossier_id
GROUP BY d.id
)
[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 TRUE
AND vp.dossier_id IS NOT NULL
AND affected_ccial.dossier_id IS NOT NULL
AND d.vente_etape_id = 1200
AND NOT d.is_certivia
AND dm_bien.dossier_id IS NULL
AND rdv_r2.dossier_id IS NULL
AND rdv_ra.dossier_id IS NULL
AND mandat_papier_non_recu.dossier_id IS NOT NULL
AND (csite_annonce_enabled.dossier_id IS NULL OR NOT csite_annonce_enabled.on_csite_enabled)
AND invalidated_estimation.dossier_id IS NULL
AND num_mandat_reserved.dossier_id IS NULL
AND NOT d_status.status_ids @> '{175}'
;
✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯

where :

✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯
-- The "WHERE" conditions are destinated to be dynamically generated by
-- an API

-- All the CTEs contain at most 55260 records.

=> WITH vp AS (
SELECT dossier.id AS dossier_id
FROM dossier, vente_process
WHERE dossier.id = vente_process.dossier_id
GROUP BY dossier.id
) select count(*) from vp;
┌───────┐
│ count │
├───────┤
│ 42792 │
└───────┘

=> select count(*) from dossier;
┌───────┐
│ count │
├───────┤
│ 55260 │
└───────┘

=> \d dossier

Table "public.dossier"
┌─────────────────────┬─────────────────────────────┬──────────────────────────────────────────────────────┐
│ Column │ Type │ Modifiers │
├─────────────────────┼─────────────────────────────┼──────────────────────────────────────────────────────┤
│ id │ integer │ not null default nextval('dossier_id_seq'::regclass) │
│ bien_id │ integer │ not null │
│ date_insert │ timestamp without time zone │ not null default now() │
│ data │ hstore │ │
│ vente_type_id │ integer │ │
│ vente_arret_id │ integer │ │
│ vente_etape_id │ integer │ not null │
│ apporteur_id │ integer │ │
│ mandat_id │ integer │ │
│ old_cpro_dossier_id │ integer │ │
│ en_contentieux │ boolean │ not null default false │
│ is_certivia │ boolean │ not null default false │
│ no_print_pool │ boolean │ not null default false │
└─────────────────────┴─────────────────────────────┴──────────────────────────────────────────────────────┘
Indexes:
"dossier_pkey" PRIMARY KEY, btree (id)
"dossier_old_cpro_dossier_id_uniq" UNIQUE CONSTRAINT, btree (old_cpro_dossier_id)
"dossier_bien_id_idx" btree (bien_id)
"dossier_date_insert_idx" btree (date_insert)
"dossier_mandat_id_idx" btree (mandat_id)
"dossier_vente_arret_id_idx" btree (vente_arret_id NULLS FIRST)
Foreign-key constraints:
"dos_bien_fk" FOREIGN KEY (bien_id) REFERENCES bien(id) ON UPDATE RESTRICT ON DELETE RESTRICT
"dos_vdt_fk" FOREIGN KEY (vente_type_id) REFERENCES vente_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT
"dos_ven_arr_id_fk" FOREIGN KEY (vente_arret_id) REFERENCES vente_arret(id) ON UPDATE RESTRICT ON DELETE SET NULL
"dossier_apporteur_id_fkey" FOREIGN KEY (apporteur_id) REFERENCES apporteur(id)
"dossier_mandat_id_fkey" FOREIGN KEY (mandat_id) REFERENCES mandat(id) ON UPDATE CASCADE ON DELETE RESTRICT
"dossier_vente_etape_id_fk" FOREIGN KEY (vente_etape_id) REFERENCES vente_etape(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED

=> select count(*) from vente_process;
┌────────┐
│ count │
├────────┤
│ 334783 │
└────────┘

=> \d vente_process

Table "public.vente_process"
┌─────────────────┬─────────────────────────────┬────────────────────────────────────────────────────────────┐
│ Column │ Type │ Modifiers │
├─────────────────┼─────────────────────────────┼────────────────────────────────────────────────────────────┤
│ id │ integer │ not null default nextval('vente_process_id_seq'::regclass) │
│ dossier_id │ integer │ not null │
│ id_reference │ integer │ │
│ table_name │ character varying(50) │ not null │
│ vente_action_id │ integer │ not null │
│ date_insert │ timestamp without time zone │ not null default now() │
│ ccial_id │ integer │ │
│ admin_id │ integer │ │
│ acq_id │ integer │ │
│ vente_etape_id │ integer │ │
│ data │ jsonb │ │
└─────────────────┴─────────────────────────────┴────────────────────────────────────────────────────────────┘
Indexes:
"vente_process_pkey" PRIMARY KEY, btree (id)
"vente_process_dossier_id_idx" btree (dossier_id)
Check constraints:
"proc_ven_ccial_admin_chk" CHECK (ccial_id IS NOT NULL OR admin_id IS NOT NULL)
Foreign-key constraints:
"pro_ven_adm_fk" FOREIGN KEY (admin_id) REFERENCES personne_employe(id) ON UPDATE RESTRICT ON DELETE RESTRICT
"pro_ven_cci_fk" FOREIGN KEY (ccial_id) REFERENCES personne_employe(id) ON UPDATE RESTRICT ON DELETE RESTRICT
"pro_ven_dos_fk" FOREIGN KEY (dossier_id) REFERENCES dossier(id) ON UPDATE CASCADE ON DELETE RESTRICT
"pro_ven_typ_act_ven_fk" FOREIGN KEY (vente_action_id) REFERENCES vente_action(id) ON UPDATE RESTRICT ON DELETE RESTRICT
"vente_process_acq_id_fkey" FOREIGN KEY (acq_id) REFERENCES personne_acq(id)
"vente_process_vente_etape_id_fk" FOREIGN KEY (vente_etape_id) REFERENCES vente_etape(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯

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

resulting in this similar query :
✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯
[CODE]
FROM dossier d
LEFT JOIN affected_ccial ON affected_ccial.dossier_id = d.id
LEFT JOIN vp ON vp.dossier_id = d.id
[CODE]
✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯✂⋯

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.

Thanks for your reading and your eventual answer,
--
PI