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