Vous n'êtes pas identifié(e).
Pages : 1
Bonjour à toutes et à tous,
Je rencontre un problème de lenteur sur une requête et je demande s'il vous plais de m'aider à la "tuner". Merci d'avance.
La requêtes est:
delete from jahia_fields_prop where fieldid_jahia_fields_prop not in (select distinct id_jahia_fields_data from jahia_fields_data);
Voici les informations sur les deux tables:
select count(*) from jahia_fields_prop;
count
--------
616254
(1 ligne)
Table « public.jahia_fields_prop »
Colonne | Type | Modificateurs
---------------------------------------+--------------------------+---------------
fieldid_jahia_fields_prop | integer | non NULL
propertyname_jahia_fields_prop | character varying(250) | non NULL
propvalue_jahia_fields_prop | character varying(50) |
Index :
"jahia_fields_prop_pkey" PRIMARY KEY, btree (fieldid_jahia_fields_prop, propertyname_jahia_fields_prop)
---------------------------------
select count(*) from jahia_fields_data;
count
---------
6589256
(1 ligne)
Table « public.jahia_fields_data »
Colonne | Type | Modificateurs
------------------------------- ----+---------------------------+---------------
id_jahia_fields_data | integer | non NULL
version_id | integer | non NULL
workflow_state | integer | non NULL
language_code | character varying(10) | non NULL
connecttype_jahia_fields_data | integer |
ctnid_jahia_fields_data | integer |
fielddefid_jahia_fields_data | integer |
id_jahia_obj | integer |
type_jahia_obj | character varying(22) |
rights_jahia_fields_data | integer |
pageid_jahia_fields_data | integer |
jahiaid_jahia_fields_data | integer |
type_jahia_fields_data | integer |
value_jahia_fields_data | character varying(2048) |
Index :
"jahia_fields_data_pkey" PRIMARY KEY, btree (id_jahia_fields_data, version_id, workflow_state, language_code)
"jahia_fields_data_index" btree (id_jahia_fields_data, workflow_state, pageid_jahia_fields_data)
"jahia_fields_data_index10" btree (id_jahia_obj, type_jahia_obj, workflow_state)
"jahia_fields_data_index11" btree (id_jahia_fields_data, workflow_state, version_id, pageid_jahia_fields_data)
"jahia_fields_data_index12" btree (fielddefid_jahia_fields_data, ctnid_jahia_fields_data, workflow_state)
"jahia_fields_data_index13" btree (jahiaid_jahia_fields_data)
"jahia_fields_data_index2" btree (pageid_jahia_fields_data, ctnid_jahia_fields_data, id_jahia_fields_data, workflow_state)
"jahia_fields_data_index3" btree (pageid_jahia_fields_data, rights_jahia_fields_data, workflow_state)
"jahia_fields_data_index4" btree (ctnid_jahia_fields_data, id_jahia_fields_data)
"jahia_fields_data_index5" btree (type_jahia_fields_data, value_jahia_fields_data, workflow_state, version_id)
"jahia_fields_data_index6" btree (id_jahia_obj, ctnid_jahia_fields_data, workflow_state)
"jahia_fields_data_index7" btree (fielddefid_jahia_fields_data, id_jahia_obj, type_jahia_obj, id_jahia_fields_data)
"jahia_fields_data_index8" btree (ctnid_jahia_fields_data, workflow_state, id_jahia_fields_data)
"jahia_fields_data_index9" btree (id_jahia_fields_data, workflow_state, language_code)
Contraintes de clés étrangères :
"fk891b251a291a9bf4" FOREIGN KEY (fielddefid_jahia_fields_data) REFERENCES jahia_fields_def(id_jahia_fields_def)
Je sais que "not in" est très coûteux mais j'ai essayé avec join mais je n'ai pas réussit à le faire. J'attends vos réponses et suggestions avec plaisir et impatience.
Bien à vous
Hors ligne
Sans présumer du reste, c'est le genre de requête qui aime bien un gros (voire très gros) work_mem. Vous pouvez essayer:
set work_mem to '256MB'; -- (voire plus, si vous avez beaucoup de RAM sur votre serveur, vous pouvez essayer 1GB)
delete from jahia_fields_prop where fieldid_jahia_fields_prop not in (select distinct id_jahia_fields_data from jahia_fields_data);
Il a besoin de pas mal de ram pour faire de distinct, puis hacher le résultat pour faire son not in. Sans le plan, difficile de vous en dire plus. Il pourrait aussi y avoir un problème de FK non indexée (ça n'a pas l'air d'être le cas ici), de trigger … c'est le genre de choses qui pourrait ralentir ce genre de requête. Donc si l'augmentation du work_mem ne suffit pas, il faudra le plan pour vérifier. Et savoir ce que vous appelez «lent».
Sinon, la 8.4.2 est vraiment vieille. Essayez au moins de mettre la dernière 8.4.
Marc.
Hors ligne
Merci Marc,
Voici le plan:
explain delete from jahia_fields_prop where fieldid_jahia_fields_prop not in (select distinct id_jahia_fields_data from jahia_fields_data);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Seq Scan on jahia_fields_prop (cost=465777.59..15607188979.52 rows=308127 width=6)
Filter: (NOT (SubPlan 1))
SubPlan 1
-> Materialize (cost=465777.59..508709.79 rows=3087220 width=4)
-> Unique (cost=0.00..450630.37 rows=3087220 width=4)
-> Index Scan using jahia_fields_data_index9 on jahia_fields_data (cost=0.00..434151.45 rows=6591567 width=4)
=============================================================================================
Concernant le upgrade ça va se faire mais pas dans les médiats car on est entrain de voir avec l'éditeur de l'application et ça sera du postgresql9.x
J'ai testé l'augmentation de work_mem "set work_mem to " ça marche mieux merci en revanche ça reste lent. Est il possible de la tuner pour éviter "not in"?
Merci d'avance.
Hors ligne
Il faudrait plutôt faire un EXPLAIN ANALYZE pour avoir les vraies valeurs en plus des estimations. Attention que, comme vous faites un DELETE, la suppression sera effective, sauf si vous commencez par un BEGIN et finissez par un ROLLBACK. Donc :
BEGIN;
EXPLAIN ANALYZE delete...
ROLLBACK;
et postez ici le résultat. Merci.
Guillaume.
Hors ligne
Le work_mem n'était pas suffisant pour la requête. Augmentez le jusqu'à ce que vous ayez un plan de ce genre:
marc=# EXPLAIN select * from t1 where a not in (select distinct a from t2);
QUERY PLAN
--------------------------------------------------------------------------
Seq Scan on t1 (cost=115126.18..132051.17 rows=500000 width=4)
Filter: (NOT (hashed SubPlan 1))
SubPlan 1
-> HashAggregate (cost=113735.00..114847.94 rows=111294 width=4)
-> Seq Scan on t2 (cost=0.00..96935.00 rows=6720000 width=4)=> Il faut que l'optimiseur ait envie de vous faire un NOT (hashed SubPlan 1), donc avoir la place pour mettre le «subplan», c'est à dire le résultat du distinct, dans une table de hachage.
Marc.
Hors ligne
Bonjour, Merci guillaume et merci Marc,
Ci après le résultat d'explain analyze. J'ai rajouté set work_mem sinon la requête n'aboutira pas (très lente).
set work_mem TO "1GB";
SET
Begin;
BEGIN
explain analyze delete from jahia_fields_prop where fieldid_jahia_fields_prop not in (select distinct id_jahia_fields_data from jahia_fields_data);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on jahia_fields_prop (cost=458348.42..471968.60 rows=308127 width=6) (actual time=6512.137..6512.137 rows=0 loops=1)
Filter: (NOT (hashed SubPlan 1))
SubPlan 1
-> Unique (cost=0.00..450630.37 rows=3087220 width=4) (actual time=0.026..3614.092 rows=5442652 loops=1)
-> Index Scan using jahia_fields_data_index9 on jahia_fields_data (cost=0.00..434151.45 rows=6591567 width=4) (actual time=0.024..2387.432 rows=
6589256 loops=1)
Total runtime: 6535.638 ms
(6 lignes)
rollback;
ROLLBACK
Bien à vous.
Hors ligne
Et 6,5s c'est suffisant ?
Sinon il y a aussi moyen de réécrire la requête comme ça:
delete from jahia_fields_prop where not exists (select 1 from jahia_fields_data where jahia_fields_data.id_jahia_fields_data=jahia_fields_prop.fieldid_jahia_fields_prop)
C'est une anti-jointure, et c'est ce que Postgres crée comme plan:
EXPLAIN ANALYZE select * from t1 where not exists (select 1 from t2 where t2.a=t1.a);
--------------------------------------------------------------------------------------------------------------------------
Hash Anti Join (cost=207185.00..272402.55 rows=888706 width=4) (actual time=1598.317..2941.993 rows=890000 loops=1)
Hash Cond: (t1.a = t2.a)
-> Seq Scan on t1 (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.019..74.888 rows=1000000 loops=1)
-> Hash (cost=96935.00..96935.00 rows=6720000 width=4) (actual time=1595.721..1595.721 rows=6720000 loops=1)
-> Seq Scan on t2 (cost=0.00..96935.00 rows=6720000 width=4) (actual time=0.013..555.524 rows=6720000 loops=1)
Total runtime: 2989.483 ms
Marc.
Hors ligne
C'est OK déjà le problème de blocage n'est plus là et c'est super. Je sort avec 2 solutions.
Je veux aussi souligner votre réactivité, disponibilité et efficacité et vous dire (guillaume et Marc) un grand MERCI.
Hors ligne
Pages : 1