[PERFORM] 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
RESTRICTDEFERRABLE 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
DELETERESTRICT 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