PostgreSQL: performance inquietante.

Le 01/07/2010 14:42, Etienne a écrit :

Salut.

j'ai un petit soucis.
J'enregistre des mails dans une base de données.
J'ai donc une table toute simple

CREATE TABLE "mail" (
"idmail" serial NOT NULL,
"idmailfolder" integer,
"idparent" integer,
"from_name" text,
"from_mail" text,
"date" timestamp without time zone DEFAULT now(),
"subject" text,
"body" text,
PRIMARY key (idmail),
FOREIGN key (idmailfolder) REFERENCES mailfolder (idobject),
FOREIGN key (idparent) REFERENCES mail (idmail)
);

l'insertion se fait sans problème a une vitesse acceptable.
par contre la suppression c'est une autre affaire

explain analyze DELETE FROM MAIL WHERE idmail = 49233;
---------------------------------------------------------------------
Index Scan using mail_pkey on mail (cost=0.00..8.28 rows=1 width=6)
(actual time=0.039..0.052 rows=1 loops=1)
Index Cond: (idmail = 49233)
Trigger for constraint mail_idparent_fkey: time5.368 calls=1
Trigger for constraint mailattach_idmail_fkey: time=4.449 calls=1
Total runtime: 169.915 ms

On voit que le contrôl sur l'idparent prend quasiment tout le temps du
DELETE.

Alors pourquoi ?
pourquoi est-ce que ca prend plus de temps que la contrainte sur le
dossier.

pour info idparent vaut toujours NULL dans tout mes mails (pour le moment)

alors on se doute que si la suppression de 1 mail prend 1/10eme de
seconde. je vous raconte pas la suppression de 1000 mails !!!

Que faire ?

tu as des clefs étrangères donc supprimer un email veut aussi dire aller
supprimer des enregistrements liés. Après, dans le cadre de
transactions, tu ne multiplies par le temps par 100 pour 100 enregistrements

Le 01/07/2010 14:46, tophe a écrit :

Le 01/07/2010 14:42, Etienne a écrit :

Salut.

j'ai un petit soucis.
J'enregistre des mails dans une base de données.
J'ai donc une table toute simple

CREATE TABLE "mail" (
"idmail" serial NOT NULL,
"idmailfolder" integer,
"idparent" integer,
"from_name" text,
"from_mail" text,
"date" timestamp without time zone DEFAULT now(),
"subject" text,
"body" text,
PRIMARY key (idmail),
FOREIGN key (idmailfolder) REFERENCES mailfolder (idobject),
FOREIGN key (idparent) REFERENCES mail (idmail)
);

l'insertion se fait sans problème a une vitesse acceptable.
par contre la suppression c'est une autre affaire

explain analyze DELETE FROM MAIL WHERE idmail = 49233;
---------------------------------------------------------------------
Index Scan using mail_pkey on mail (cost=0.00..8.28 rows=1 width=6)
(actual time=0.039..0.052 rows=1 loops=1)
Index Cond: (idmail = 49233)
Trigger for constraint mail_idparent_fkey: time5.368 calls=1
Trigger for constraint mailattach_idmail_fkey: time=4.449 calls=1
Total runtime: 169.915 ms

On voit que le contrôl sur l'idparent prend quasiment tout le temps du
DELETE.

Alors pourquoi ?
pourquoi est-ce que ca prend plus de temps que la contrainte sur le
dossier.

pour info idparent vaut toujours NULL dans tout mes mails (pour le
moment)

alors on se doute que si la suppression de 1 mail prend 1/10eme de
seconde. je vous raconte pas la suppression de 1000 mails !!!

Que faire ?

tu as des clefs étrangères donc supprimer un email veut aussi dire aller
supprimer des enregistrements liés. Après, dans le cadre de
transactions, tu ne multiplies par le temps par 100 pour 100
enregistrements

Salut.
oui j'ai des clé etrangère, mais celle qui pose réellement problème
c'est celle qui pointe sur la même table (idparent).

Visiblement sur internet il y a d'autre personnes qui ont le meme
problème mais pas vraiement de solution.

Est ce que si je supprime la contrainte, exsecute mes delete et replace
la contrainte tout cela dans une transaction cela va marcher ???

Ce qui est sur, c'est que le suppression de la contrainte ressout mon
problème de performance...

Etienne

Le 01/07/2010 14:46, tophe a écrit :

Alors pourquoi ?
pourquoi est-ce que ca prend plus de temps que la contrainte sur le
dossier.

Et tout cas j'ai ajouté un idex sur idparent et ca a résolu le problème
meme si je n'arrive pas a comprendre pourquoi...

Etienne

Le Thu, 01 Jul 2010 14:42:02 +0200, Etienne a écrit:

J'enregistre des mails dans une base de données. J'ai donc une table
toute simple

La sérialisation des emails n'est pas forcément chose triviale si on veut
tout gérer, et selon le degré de conservation d'informations qu'on
souhaite.
Il faut notamment se poser la question des fichiers attachés éventuels,
des mails en multipart, des jeux de caractères, etc.

CREATE TABLE "mail" (
"idmail" serial NOT NULL,
"idmailfolder" integer,
"idparent" integer,
"from_name" text,
"from_mail" text,
"date" timestamp without time zone DEFAULT now(), "subject" text,
"body" text,
PRIMARY key (idmail),
FOREIGN key (idmailfolder) REFERENCES mailfolder (idobject),

FOREIGN

key (idparent) REFERENCES mail (idmail)
);

l'insertion se fait sans problème a une vitesse acceptable. par contre
la suppression c'est une autre affaire

Le PRIMARY KEY sur idmail implique UNIQUE qui implique un index.
Lors de l'insertion, la vérification de contrainte relative à idparent
est donc rapide grâce à l'index.
J'imagine qu'il en est de même pour mailfolder.idobject

explain analyze DELETE FROM MAIL WHERE idmail = 49233;
---------------------------------------------------------------------
Index Scan using mail_pkey on mail (cost=0.00..8.28 rows=1 width=6)
(actual time=0.039..0.052 rows=1 loops=1)
Index Cond: (idmail = 49233)
Trigger for constraint mail_idparent_fkey: time5.368 calls=1
Trigger for constraint mailattach_idmail_fkey: time=4.449 calls=1
Total runtime: 169.915 ms

On voit que le contrôl sur l'idparent prend quasiment tout le temps du
DELETE.

Alors pourquoi ?

Pour l'exemple donné, comparez:

EXPLAIN ANALYZE SELECT * FROM mailfolder WHERE idobjectI233;
EXPLAIN ANALYZE SELECT * FROM mail WHERE idparentI233;

pourquoi est-ce que ca prend plus de temps que la contrainte sur le
dossier.

Les 2 références ne pointent pas sur la même table, et les valeurs n'ont
probablement pas la même distribution. Les 2 explains précédents
devraient montrer des comportements et des temps bien différents...

pour info idparent vaut toujours NULL dans tout mes mails (pour le
moment)

Il pourrait donc éventuellement être intéressant de gérer ca dans une
autre table:

CREATE TABLE mail_descendance (
idparent integer NOT NULL,
idchild integer NOT NULL,
PRIMARY KEY (idparent,idchild),
FOREIGN KEY idparent REFERENCES mail(idmail),
FOREIGN KEY idchild REFERENCE mail(idmail))

+ un index sur idchild.

Ca vous évitera d'avoir des NULL dans mail. Les NULL ont leur usage, mais
quand il y en a "trop" il faut réfléchir et se demander si c'est normal.

Que faire ?

Comme vous l'avez découvert, vous êtes dans le cas canonique où un index
est intéressant. A priori dès qu'il y a une référence d'intégrité, un
index est *probablement* (toute règle a ses exceptions) intéressant.

A noter que vous pouvez faire des index fonctionnels:
CREATE INDEX toto ON mail(idparent) WHERE idparent IS NOT NULL;

--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
<http://www.dotandco.net/ressources/icann_registrars/prices>
<http://icann-registrars-life.dotandco.net/>

Le 02/07/2010 02:12, Patrick Mevzek a écrit :

La sérialisation des emails n'est pas forcément chose triviale si on veut
tout gérer, et selon le degré de conservation d'informations qu'on
souhaite.
Il faut notamment se poser la question des fichiers attachés éventuels,
des mails en multipart, des jeux de caractères, etc.

Alors oui, je confirme que ce n'est pas simple.
Il faut déjà arriver à tout encoder en utf-8 pour que postgres puisse
enregistrer dans un champs texte...
En fait j'utilise une base de données postgresql pour diverse raison
- indexation du mail avec un tsvector.
- limiter les requètes aux serveur de mail.

EXPLAIN ANALYZE SELECT * FROM mailfolder WHERE idobjectI233;
EXPLAIN ANALYZE SELECT * FROM mail WHERE idparentI233;

Les 2 références ne pointent pas sur la même table, et les valeurs n'ont
probablement pas la même distribution. Les 2 explains précédents
devraient montrer des comportements et des temps bien différents...

Heu non, plus depuis que j'ai mis un index sur ma colonne parent.

Il pourrait donc éventuellement être intéressant de gérer ca dans une
autre table:

CREATE TABLE mail_descendance (
idparent integer NOT NULL,
idchild integer NOT NULL,
PRIMARY KEY (idparent,idchild),
FOREIGN KEY idparent REFERENCES mail(idmail),
FOREIGN KEY idchild REFERENCE mail(idmail))

Oui et non.
en effet on aurait un gain (sans doute) sur la taille de la base de
données, mais une jointure de plus lors de l'utilisation de idparent
(qui soit dis en passant est assez rare puisqu'il s'ahit d'afficher les
discussion.

Ca vous évitera d'avoir des NULL dans mail. Les NULL ont leur usage, mais
quand il y en a "trop" il faut réfléchir et se demander si c'est normal.

A noter que vous pouvez faire des index fonctionnels:
CREATE INDEX toto ON mail(idparent) WHERE idparent IS NOT NULL;

Ah... on en apprend tous les jours !
Dans mon cas cela n'est pas opportun, mais ca pourrait servir un jour.

Merci en tout cas.

Le Sat, 03 Jul 2010 12:27:21 +0200, WebShaker a écrit:

Il pourrait donc éventuellement être intéressant de gérer ca dans une
autre table:

CREATE TABLE mail_descendance (
idparent integer NOT NULL,
idchild integer NOT NULL,
PRIMARY KEY (idparent,idchild),
FOREIGN KEY idparent REFERENCES mail(idmail), FOREIGN KEY idchild
REFERENCE mail(idmail))

Oui et non.
en effet on aurait un gain (sans doute) sur la taille de la base de

Je ne visais pas tant un gain de taille (c'est rarement très intéressant,
les disques durs sont "illimités" de nos jours, et on a quand même peu
souvent des bases de données dont la taille réelle utile est supérieure à
la RAM disponible), mais de performances (y compris lors de la
suppression, votre problème initial).

données, mais une jointure de plus lors de l'utilisation de idparent

Sauf que vous dites que idparent est souvent null (donc ca serait une
jointure type OUTER), et d'autre part avec les index, la jointure serait
rapide (la table mail_descendance risquant d'être toute petite d'après ce
que vous dites, elle tiendra en permanence intégralement en RAM).

Il ne faut pas avoir peur des jointures (un "tic" que je vois souvent
chez mes étudiants), c'est la base du monde relationnel, et l'équivalent
des boucles dans les langages de programmation...

(qui soit dis en passant est assez rare puisqu'il s'ahit d'afficher les
discussion.

Comment vous faites justement pour afficher les fils (threads) de
discussion, sans récupération de la descendance ?

A noter que vous pouvez faire des index fonctionnels: CREATE INDEX toto
ON mail(idparent) WHERE idparent IS NOT NULL;

Ah... on en apprend tous les jours !
Dans mon cas cela n'est pas opportun, mais ca pourrait servir un jour.

Cela me semble opportun vu que vous dites justement que idparent vaut
souvent/toujours NULL, et ca ne s'indexe pas.

Quoiqu'il en soit, à toutes fins utiles :
http://www.postgresql.org/docs/8.4/interactive/indexes-partial.html
et
http://www.postgresql.org/docs/8.4/interactive/indexes-expressional.html

--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
<http://www.dotandco.net/ressources/icann_registrars/prices>
<http://icann-registrars-life.dotandco.net/>