une requête de suppression qui ne fonctionne pas (trop longue)

[Michael REMY]

bonjour,

j'ai une table avec un shéma très simpe :

id serial NOT NULL,
wkf_id integer,
uid integer,
res_id integer,
res_type character varying(64),
state character varying(32) DEFAULT 'active'::character varying,
CONSTRAINT wkf_instance_pkey PRIMARY KEY (id),
CONSTRAINT wkf_instance_wkf_id_fkey FOREIGN KEY (wkf_id)
REFERENCES wkf (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE SET NULL
)
WITHOUT OIDS;

je souhaite exécuter cette requête :

delete from wkf_instance where res_type='cpn.account.hour'

et elle prend plus de 5min voire plus car j'ai arrêté le process croyant que ça plante (cpu à 100%)

il ya dans la table 700000 enregistrement à supprimer.
La table fait 56M, l'index 13M alors que le serveur est puissant et à 2go de mémoire.

où est le problème ?

si ça peut aider, voilà quelques infos durant le process :

[estofilo]

Que dit l'EXPLAIN de la requête?

[Michael REMY]

Envoyé par estofilo

Que dit l'EXPLAIN de la requête?

comme elle ne se termine pas, je ne peux pas avoir l'explain analyse !

[estofilo]

c'est pourquoi il faut faire EXPLAIN delete... et non EXPLAIN ANALYZE delete...

[estofilo]

Est-ce qu'il y a des clefs étrangères qui pointent vers la table dont les lignes sont supprimées par la requête?

[scheu]

Comme le dit estofilo, regarde l'explain de ta requête, si ça se trouve il fait un index range scan sur un index inapproprié
Peut-être aussi as-tu trop d'indexes sur cette table, dans ce cas la suppression de lignes est longue car doit aussi mettre à jour les indexes

[Michael REMY]

bonjour,

j'ai fait un explain mais pour 50 éléments, voiçi le résultat (ça dure 30s sans le analyse) :

"Nested Loop (cost=1.78..3155.99 rows=48 width=6)"
" -> HashAggregate (cost=1.78..2.28 rows=50 width=4)"
" -> Limit (cost=0.00..1.15 rows=50 width=4)"
" -> Seq Scan on wkf_instance (cost=0.00..14736.52 rows=639397 width=4)"
" Filter: ((res_type)::text = 'cpn.account.hour'::text)"
" -> Index Scan using wkf_instance_pkey on wkf_instance (cost=0.00..63.06 rows=1 width=10)"
" Index Cond: (wkf_instance.id = "outer".id)"
" Filter: ((res_type)::text = 'cpn.account.hour'::text)"

oui il ya 5 tables qui sont ratachées à cette table.

ps : j'ai regardé la doc, mais ya pas moyen de ajouter une clause LIMIT dans le delete ? je voudrais les supprimer 10 par 10 chaque minute.

[SQLpro]

1) rien ne vous empêche de calculer un nombre approximatif de DELETE en jouant sur l'auto incrément. Il n'y a pas de LIMIT avec PostGreSQL pour la mise à jour.
2) tentez un index en hash sur (res_type, wkf_id )
3) si vous faites vos DELETE aux heures creuses, une technique consiste à :

• démarrez une transaction au niveau SERIALIZABLE
• supprimer tous les index non sémantiques
• faire votre DELETE
• rétablir les index
• valider la transaction

A +

[estofilo]

A propos du temps d'exécution, si on synthétise les infos de la discussion:
- 700000 lignes à effacer dans une table wkf_instance
- 5 tables référencent wkf_instance en clef étrangère
- une clause ON DELETE SET NULL sur des tables qui référencent wkf_instance

Pour chaque ligne à effaçer, postgres doit lancer en interne 5 recherches dans les tables "fille" et pour chaque ligne trouvée dans ces tables, faire un UPDATE d'une colonne à NULL.

Ca représente donc 700k*5 = 3,5 millions de requêtes SELECT + un nombre de requêtes UPDATE supposément de même ordre de grandeur, ce qui explique pourquoi c'est lent.

[estofilo]

Envoyé par Michael REMY

ps : j'ai regardé la doc, mais ya pas moyen de ajouter une clause LIMIT dans le delete ? je voudrais les supprimer 10 par 10 chaque minute.

La technique classique est de faire

Code :

1
2
 DELETE FROM TABLE WHERE clef_primaire IN
 (SELECT clef_primaire FROM TABLE WHERE ... LIMIT 10)