Cela a mis un peu de temps récemment, à la fois sur soi et sur l'PostgreSQL listes de diffusion.
Le TL;DR pour vos deux derniers points:
(a) Le plus grand shared_buffers est peut-être pourquoi TRONQUER est plus lent sur le serveur CI. Différents fsync de configuration ou de l'utilisation de la rotation des disques à la place des disques Ssd pourrait aussi être en cause.
(b) TRUNCATE a un coût fixe, mais pas nécessairement plus lent que l' DELETE, en plus il n'a plus de travail. Voir l'explication détaillée qui suit.
Mise à JOUR: UN important travail de réflexion sur pgsql-performance est née de ce post. Voir ce fil de discussion.
Mise à JOUR 2: des Améliorations ont été ajoutées à 9.2beta3 qui devraient vous aider avec cela, voir ce post.
Explication détaillée de l' TRUNCATE vs DELETE FROM:
Bien que n'étant pas un expert sur le sujet, ma compréhension est qu' TRUNCATE a près d'un coût fixe par la table, tandis que DELETE d'au moins O(n) pour n lignes; pire si il y a des clés étrangères référençant la table en cours de suppression.
J'ai toujours supposé que le coût fixe d'un TRUNCATE a été plus faible que le coût d'un DELETE sur un quasi-vide de la table, mais ce n'est pas vrai du tout.
TRUNCATE table; n'est plus de DELETE FROM table;
L'état de la base de données après un TRUNCATE table est à peu près le même que si vous voulez lancer à la place:
DELETE FROM table;-
VACCUUM (FULL, ANALYZE) table; (9.0+ uniquement, voir note de bas de page)
... bien sûr, TRUNCATE n'a pas de produire ses effets avec un DELETE et VACUUM.
Le point est que, DELETE et TRUNCATE faire des choses différentes, de sorte que vous n'êtes pas juste de la comparaison de deux commandes avec les mêmes résultats.
Un DELETE FROM table; permet aux morts de lignes et de ballonnements, de rester, permet à l'index pour transporter les morts entrées, n'est-ce pas mettre à jour le tableau des statistiques utilisées par le planificateur de requête, etc.
Un TRUNCATE vous donne un tout nouveau tableau et index comme s'ils étaient juste CREATEed. C'est comme vous avez supprimé tous les dossiers, réindexée la table et fait un vide complet.
Si vous n'avez pas de soins si il y a crud à gauche dans le tableau parce que vous êtes sur le point d'aller et de le remplir de nouveau, vous peut-être mieux de les utiliser DELETE FROM table;.
Parce que vous n'êtes pas en cours d'exécution VACCUM vous trouverez que la mort des lignes et des entrées d'index s'accumulent comme le ballonnement qui doivent être analysés alors ignorés, ce qui ralentit toutes vos requêtes vers le bas. Si vos tests ne sont pas réellement de créer et de supprimer tout ce qui est la quantité de données que vous pouvez ne pas remarquer ou de soins, et vous pouvez toujours faire un VACCUM ou les deux à mi-chemin par le biais de votre essai si vous le faites. Mieux, laissez agressif autovaccum en sorte que autovaccum le fait pour vous dans le fond.
Vous pouvez toujours TRUNCATE tous vos tables, après l' ensemble de la suite de test s'exécute pour s'assurer qu'aucun des effets de construire à travers de nombreuses pistes. Sur 9.0 et versions plus récentes, VACUUM (FULL, ANALYZE); à l'échelle mondiale sur la table est au moins aussi bonne, sinon meilleure, et c'est un ensemble beaucoup plus facile.
IIRC Pg a quelques optimisations qui signifie qu'il remarquerez peut-être que votre transaction est le seul qui peut voir la table et tout de suite marquer les blocs comme libre de toute façon. Dans le test, quand j'ai voulu créer la météorisation j'ai eu d'avoir plus d'une connexion simultanée à le faire. Je ne voudrais pas compter sur cela, cependant.
DELETE FROM table; est très bon marché pour les petites tables avec pas de f/k refs
D' DELETE de tous les enregistrements d'une table sans clé étrangère références, toutes Pg à faire un séquentiel de la table de balayage et de définir l' xmax de la n-uplets rencontrés. C'est un très bon marché de l'opération - essentiellement linéaire d'une lecture et d'une semi-linéaire de l'écriture. Autant que je sache, il n'a pas à toucher à l'index; ils continuent de pointer vers la mort tuples jusqu'à ce qu'ils sont nettoyés par un plus tard VACCUM qui marque également les blocs dans le tableau ne contenant que des morts tuples, comme gratuit.
DELETE seulement se coûteux si il y a beaucoup de dossiers, si il y a beaucoup de clés étrangères des références qui doit être vérifié, ou si vous comptez le subséquentes VACUUM (FULL, ANALYZE) table; nécessaires pour correspondre TRUNCATEs'effets dans le coût de votre DELETE .
Dans mes tests, ici, un DELETE FROM table; a été généralement 4x plus rapide que l' TRUNCATE à 0,5 ms vs 2ms. C'est un essai DB sur un SSD, en cours d'exécution avec fsync=off parce que je n'aime pas si je perdre toutes ces données. Bien sûr, DELETE FROM table; ne le fait pas tous le même travail, et si j'ai suivi avec un VACCUM (FULL, ANALYZE) table; c'est beaucoup plus cher 21ms, de sorte que l' DELETE est seulement une victoire si je n'ai pas vraiment besoin de la table pristene.
TRUNCATE table; fait beaucoup plus de coûts fixes, le travail et le ménage qu' DELETE
En revanche, une TRUNCATE a à faire, beaucoup de travail. Elle doit allouer de nouveaux fichiers de la table, sa table TOAST le cas échéant, et tous les index de la table a. Les en-têtes doivent être écrites dans les fichiers et les catalogues système nécessitant une mise à jour trop (pas sûr sur ce point, vous n'avez pas vérifié). Il a alors à remplacer les anciens fichiers par les nouveaux, ou de supprimer les anciennes, et doit assurer le système de fichier a suivi les changements avec une opération de synchronisation - fsync() ou similaire - qui, généralement, vide tous les tampons sur le disque. Je ne suis pas sûr de savoir si la synchronisation est ignoré si vous êtes en cours d'exécution avec les données (manger) option fsync=off .
J'ai appris récemment que l' TRUNCATE doit également vider tous PostgreSQL en mémoire liés à l'ancienne table. Cela peut prendre un montant non négligeable de temps avec d'énormes shared_buffers. Je suppose que c'est pourquoi il est plus lent sur votre serveur CI.
L'équilibre
De toute façon, vous pouvez voir qu'un TRUNCATE d'une table à laquelle est associée une table TOAST (la plupart le font) et de plusieurs index peut prendre quelques instants. Pas longtemps, mais plus d'un DELETE d'un quasi-vide de la table.
Par conséquent, vous pourriez être mieux de faire un DELETE FROM table;.
--
Remarque: sur la DBs avant 9.0, CLUSTER table_id_seq ON table; ANALYZE table; ou VACCUM FULL ANALYZE table; REINDEX table; serait plus près équivalent à TRUNCATE. L' VACUUM FULL impl changé pour un bien meilleur en 9.0.
