J'ai quelques relations entre des tables qui sont toutes liées à une table "propriétaire". Donc, pour les besoins de l'exemple :
- Propriétaire de la table avec PK id
- Table Parent avec PK id et FK owner_id se référant à Owner.id, avec un index sur celle-ci, et
ON DELETE CASCADE. - Table Child avec PK id et FK parent_id faisant référence à Parent.id, avec un index sur celle-ci, et
ON DELETE CASCADE.
La table Child est énorme (~50 millions de lignes), la table Parent a quelques milliers de lignes, et la table Owner est très petite (~10 lignes).
Il existe quelques autres tables liées au propriétaire et au parent, mais elles sont relativement petites (quelques milliers) et ont également des index sur des clés étrangères, et ON CASCADE DELETE .
Parfois, lorsque je supprime une ligne de propriétaire, la suppression en cascade de toutes les lignes (environ 12 millions de lignes d'enfant et un millier de lignes de parent) fonctionne très rapidement (quelques secondes), mais parfois cela prend presque une heure.
Comment puis-je trouver la cause de ce problème ? J'ai fait explain sur delete from child where parent_id in (select id from parent where owner_id = 1) où 1 est l'identifiant d'une des lignes du propriétaire (j'ai essayé plusieurs identifiants pour être sûr) et il dit qu'il utilise Bitmap Heap Scan -> Bitmap Index Scan et Index Scan. Cependant, je ne suis pas sûr que j'imite ce qui est réellement fait lorsqu'il y a un numéro d'identification de propriétaire. ON DELETE CASCADE déclencher. Comment puis-je comprendre ce qui cause ces énormes retards ? Se pourrait-il que Postgres préfère parfois effectuer un balayage séquentiel (en raison du nombre de lignes) ?
L'insertion des mêmes lignes ne prend que 8 minutes (y compris la logique de l'application et quelques milliers de validations de transactions). Je n'arrive donc pas à comprendre pourquoi la suppression directe prend autant de temps.
J'utilise Postgres 9.1.6.
