pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedChaotic query planning ?
3+ messages / 2 participants
[nested] [flat]
* Chaotic query planning ?
@ 2017-01-19 14:37 Philippe Ivaldi <pivaldi@ovya.fr>
0 siblings, 1 reply; 3+ messages in thread
From: Philippe Ivaldi @ 2017-01-19 14:37 UTC (permalink / raw)
To: pgsql-performance
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Chaotic query planning ?
@ 2017-01-19 16:05 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Philippe Ivaldi <pivaldi@ovya.fr>
0 siblings, 1 reply; 3+ messages in thread
From: Albe Laurenz @ 2017-01-19 16:05 UTC (permalink / raw)
To: 'Philippe Ivaldi *EXTERN*' <pivaldi@ovya.fr>; pgsql-performance
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Chaotic query planning ?
@ 2017-01-30 08:24 Philippe Ivaldi <pivaldi@ovya.fr>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 0 replies; 3+ messages in thread
From: Philippe Ivaldi @ 2017-01-30 08:24 UTC (permalink / raw)
To: pgsql-performance; +Cc: Albe Laurenz <laurenz.albe@wien.gv.at>
Albe Laurenz wrote
> […]
> Experiment with raising join_collapse_limit and from_collapse_limit to 11.
Thank you, this solve the problem.
> Alternatively, optimize the join order by hand and don't tune the parameters.
What is surprising is that there is no apparent/logical optimal strategy.
Best regards,
--
PI
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-01-30 08:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-01-19 14:37 Chaotic query planning ? Philippe Ivaldi <pivaldi@ovya.fr>
2017-01-19 16:05 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2017-01-30 08:24 ` Philippe Ivaldi <pivaldi@ovya.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox