[POSTGRESQL] explain analyze

Le Mon, 20 Sep 2010 11:47:22 +0200, Etienne a écrit:

Bon ben non, les indexes n'ont rien changé malheureusement...

Vous avez fait un ANALYZE ? Et comparé les 2 plans minutieusement ?
Vos tables ont suffisamment de données ? Des NULL ? Des répartissions
statistiques "originales" ?

bon apres
la requete va deja vite, c'est juste qu'elle est un peu appelé plusieurs
millier de fois par jours, alors la moindre optimisation est directement
perceptible.

1000 fois par jour ca fait moins d'une fois par minute, donc tant que la
requête ne prends pas plusieurs minutes... :-)

--
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 Mon, 20 Sep 2010 11:47:22 +0200, Etienne a écrit:

Le 20/09/2010 11:38, Etienne a écrit :

Le 19/09/2010 18:20, SQLpro a écrit :

Si ces index n'existent pas :

eproc_tarif (idcatalog, idproduct);
product (idobject, _producer)
active (active_fr, idactive)

Bon ben non, les indexes n'ont rien changé malheureusement...

Si je reprends votre requête initiale reformatée :

SELECT DISTINCT product._producer
FROM eproc_tarif
INNER JOIN product ON (eproc_tarif.idproduct = product.idobject)
LEFT OUTER JOIN active ON (product.idactive = active.idactive)
WHERE
eproc_tarif.idcatalog = 1392086
AND active.active_fr = 't'
ORDER BY product._producer;

(Je ne suis pas sûr de bien comprendre l'intérêt de la table active dans
l'histoire vu que c'est un LEFT OUTER JOIN et que vous ne gardez que
product._producer, donc rien de la table active, pourquoi donc cette
jointure ?)

Essayez en décomposant et en regardant si vous y gagnez à chaque étape
avec un index sur les champs pertinents.

SELECT idproduct FROM eproc_tarif WHERE eproc_tarif.idcatalog = 1392086
C'est mieux avec un index sur idcatalog, ou pas ?

SELECT product._producer
FROM eproc_tarif
INNER JOIN product ON (eproc_tarif.idproduct = product.idobject)
WHERE eproc_tarif.idcatalog = 1392086
C'est mieux avec des index sur idproduct, idcatalog et idobject ou pas ?

SELECT idactive FROM product
WHERE product.idobject IN (SELECT idproduct FROM eproc_tarif WHERE
eproc_tarif.idcatalog = 1392086)
C'est mieux avec des index sur idobject et idcatalog ou pas ?

etc...

Essayez de remplacer le DISTINCT par un GROUP BY aussi.

Une fois que vous avez fait des tests avec quelques valeurs "typiques"
vous pouvez alors tester plus généralement.

Bon courage.

--
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 Mon, 20 Sep 2010 11:38:25 +0200, Etienne a écrit:

Mais pour aller un peu plus loin, est ce que cela veut dire que pour
améliorer une jointure, il faut faire un index sur deux colonnes (les
deux colonnes liant les deux tables) ?

Règle générale (qui a ses exceptions, mais ca donne un point de départ) :

- toute table de moins de quelques milliers de tuples n'a pas besoin
d'index
- toute clef primaire ou étrangère doit être indexée
(pour la clef primaire c'est automatique chez PostgreSQL puisque PRIMARY
KEY implique UNIQUE qui implique la création d'un index)
Pour les clefs étrangères c'est nécessaire puisque toute suppression du
côté de la table référencée peut nécessiter un parcours de *l'ensemble*
des (éventuellement nombreuses) tables référantes avec des clefs
étrangères
- tout élément présent dans un WHERE, suffisamment "sélectif", et en
premier lieu les données de type entières, sans (trop de) valeur NULL,
avec suffisamment de valeurs distinctes,
surtout si elles aparaissent dans plusieurs requêtes

(donc lors d'une jointure, les 2 points précédents s'additionnent pour
vous encourager très vivement à créer les index)

BTW puisque je fouille le polycopié que je donne à mes étudiants, j'ai
retrouvé cette information qui peut aider aussi :
« On peut définir la sélectivité d'un index comme étant le rapport du
nombre de valeurs distinctes indexées par le
nombre total de tuples dans la table considérée. S'il vaut 1 on est en
présence d'un index unique, et on estime que
pour toute valeur supérieure à 0.1 le SGBDR a de bonnes chances
d'utiliser l'index pour les recherches. »

HTH,

--
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 Tue, 21 Sep 2010 21:41:53 +0200, WebShaker a écrit:

La table active permettait de savoir si un produit était actif ou pas.
donc elle est utile car je ne souhaite pas conserver les marques pour

Sauf que le LEFT OUTER fait que vous conservez toutes les lignes
nécessairement. Donc il y a quelque chose qui ne colle pas entre ce que
vous dites vouloir faire et ce que vous faites...

Essayez en décomposant et en regardant si vous y gagnez à chaque étape
avec un index sur les champs pertinents.

SELECT idproduct FROM eproc_tarif WHERE eproc_tarif.idcatalog = 1392086
C'est mieux avec un index sur idcatalog, ou pas ?

Bitmap Heap Scan on eproc_tarif (cost7.34..2102.01 rowsX14

5814 lignes... ce sont des petites tables !
Ca tient entiérement en RAM, même un accès séquentiel sera rapide.
Pas la peine de se batailler avec des index...

c'est rigolo ca.
je n'ai pas d'index sur idcatalog par contre j'en ai une sur (idcatalog,
idproduct).
Je ne pensais pas qu'un index sur deux colonnes pouvait être utilisé ici
!

Dans le cas d'un index sur n-colonnes il peut être utilisé lors d'une
recherche impliquant les m premières colonnes (m < n) avec les éléments
dans le même ordre.

Essayez de remplacer le DISTINCT par un GROUP BY aussi.

DISTINCT et GROUP BY même résultat.

Oui même résultat mais pas forcément même plan ni même performances selon
comment le GROUP BY intervient.

Finalement, j'ai intégré ma table active dans la table product en
remplacent ca par un fameux bitfield tellement... cool :)

Bof; Je ne suis toujours pas convaincu du cas dénormalisé.
Et/ou je n'ai pas bien compris votre schéma et besoin, désolé.

donc la c'est logique une jointure de moins ca va plus vite je passe de
135ms a 103ms.

Ca reste le même ordre de grandeur. Ca fait beaucoup d'énergie dépensée
pour un gain minimal. Il y a certainement d'autres endroits avec des
optimisations plus intéressantes à faire...

Mais comme (et là c'est quand même mystérieux) vous venez de me montrer
que un IN peut parfois (toujours peut-être) aller plus vite qu'un INNER
JOIN ben j'ai modifié ma requete qui devient

Les deux ne retournent pas nécessairement les mêmes résultats...

--
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 Wed, 22 Sep 2010 22:33:31 +0200, WebShaker a écrit:

Sauf que le LEFT OUTER fait que vous conservez toutes les lignes
nécessairement. Donc il y a quelque chose qui ne colle pas entre ce que
vous dites vouloir faire et ce que vous faites...

oui mais au final le where active.active_fr = 't' finissait par enlever
les lignes.

Avec un LEFT OUTER JOIN sur la table active ?
Bah non, par définition, ca "n'enlève" aucune ligne, il y a autant de
lignes dans le résultat que dans la table à gauche du LEFT OUTER.
Pas moins. Il y en aurait, potentiellement moins, avec un INNER JOIN où
la condition de jointure ne serait pas réalisée.

5814 lignes... ce sont des petites tables ! Ca tient entiérement en
RAM, même un accès séquentiel sera rapide. Pas la peine de se batailler
avec des index...

Il n'y a pas de petite optimisation :)

« Premature optimization is the root of all evil ».

Dans le cas d'un index sur n-colonnes il peut être utilisé lors d'une
recherche impliquant les m premières colonnes (m< n) avec les éléments
dans le même ordre.

Ben on en apprends tous les jours :)

Il faut aussi un test "cumulé", style un AND.
a=5 AND b=6 utilisera (potentiellement) l'index (a,b)
a=5 OR b=6 ne pourra pas l'utiliser (en tout cas avec PostgreSQL et vu
comment les index sont fabriqués, pour les autres SGBDRs je ne sais pas
car comme « J'ai les goûts les plus simples du monde, je me contente du
meilleur » donc je ne m'occupe que de PostgreSQL :-))

Finalement, j'ai intégré ma table active dans la table product en
remplacent ca par un fameux bitfield tellement... cool :)

Bof; Je ne suis toujours pas convaincu du cas dénormalisé. Et/ou je
n'ai pas bien compris votre schéma et besoin, désolé.

ben en fait chaque produit est disponible (ou pas) selon les pays.

Donc ma table active permettait simplement de dire si le produit faisait
partie du catalogue francais, anglais, belge, ...

Donc potentiellement ~300 bits...
Clairement une forme normale classique ferait bien mieux l'affaire selon
moi...

J'avais décidé de mettre ca dans une table à part qui avait autant de
champs booleens que de pays.

Ah mais non c'est pas normalisé non plus ca !

CREATE TABLE produit (
id serial8,
...
);

CREATE TABLE pays (
id serial4,
name text,
....
);

CREATE TABLE disponibilite (
produit_id int8,
pays_id int8
);

+ les définitions de clefs primaires et étrangères.

Avec la jointure vous n'aurez jamais que 2 colonnes:
tel produit est dans tel pays
répété par tous les pays concernés.

Sans compter que cela passe aisément à l'échelle dès qu'il faut gérer
autre chose que des pays (commes des régions).

Alors j'aurai pu mettre les champs booleens dans la table product sans
me compliquer la vie avec mon champs de bit, mais... pour une raison
esthetique je préfère mon champs de bit qui m'évite d'avoir x colonnes
lorsque je fais un SELECT * FROM product sous le shell de postgres.

Pour l'esthétique, regardez la commande pset dans psql.
Et notamment le format "aligned".

Je croyais cependant que c'étaient les performances qui importaient...
Les performances ou l'esthétique :-) ? Ce n'est pas évident de trouver
des solutions élégantes et pertinentes, il faut choisir parfois...

Alors ok, c'est pas une raison valable, mais c'est pourtant la mienne :)

Vous allez me dire qu'avec une vue, je peux remédier au problème. Mais
je ne suis pas fan des vues. Je trouve ça pénible à manipuler.

Cela se manipule pourtant exactement comme une table dans un SELECT.

--
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 Fri, 24 Sep 2010 09:23:27 +0200, WebShaker a écrit:

Le Wed, 22 Sep 2010 22:33:31 +0200, WebShaker a écrit: Avec un LEFT
OUTER JOIN sur la table active ? Bah non, par définition, ca "n'enlève"
aucune ligne, il y a autant de lignes dans le résultat que dans la
table à gauche du LEFT OUTER. Pas moins. Il y en aurait,
potentiellement moins, avec un INNER JOIN où la condition de jointure
ne serait pas réalisée.

hum. alors ja vais peut etre dire une connerie, mais avec mon LEFT OUTER
JOIN j'aurai au pire des ligne de produit avec les données de la table
active valant toutes NULL non?

donc active.active_fr valant NULL pour ce ligne il ne peut pas valloir
't'

Le but d'une jointure normalement est quand même de récupérer des champs
stockés dans une table "annexe". Or vous faites un SELECT d'un seul
attribut de la table principale. Donc cela pourrait éventuellement
indiquer que la jointure n'est pas la requête la mieux adaptée pour ce
cas de figure et/ou que la modélisation est améliorable.

« Premature optimization is the root of all evil ».

Je la met dans mon book de citation.
C'est de qui ?

Un grand classique de l'informatique, par le grand D. Knuth lui-même.

« J'ai les goûts les plus simples du monde, je me contente du meilleur
»

Celle la aussi je la note :)

Remerciez Oscar Wilde

Oui j'ai bien compris, n'empèche que ca ira moins vite car ca fera plus
de jointure.

Je ne sais pas d'où vient cette idée largement répandue. J'ai peur que ca
vienne surtout de gens confondant SGBDR respectueux du SQL avec MySQL,
notamment lors des époques MySQL 3 et 4.

Bref, en résumé, les jointures et les opérations mathématiques sous-
jacentes sont la base du monde relationnel. On peut ne pas aimer, et ca
devient même une mode (« NoSQL » & co), mais sinon en tenant compte des
besoins de normalisation, la jointure est nécessaire et pas un mal
nécessaire.

C'est fait un choix (peut etre pas top) lié à la
performance.

Vérifié en pratique par des chiffres et des tests, ou ce sont juste des
intuitions ?

Je croyais cependant que c'étaient les performances qui importaient...
Les performances ou l'esthétique :-) ? Ce n'est pas évident de trouver
des solutions élégantes et pertinentes, il faut choisir parfois...

En effet...
Je me rassure en me disant que le champ de bit prend (en plusà moins de
place mémoire...

Vous travaillez sur l'historique plate-forme de Bill Gates avec 640 Ko ?

Cela se manipule pourtant exactement comme une table dans un SELECT.

Oui je ne nie pas que c'est bien, mais prenons mon cas. j'ai une vue
(car j'en ai quand même)

CREATE VIEW product_fr AS SELECT * FROM product WHERE (actif_bitfield &
1) = 1;

qui me permet d'avoir le catalogue francais. Et bien à chaque fois que
je modifier la table product, je suis bon pour modifier aussi ma vue
puisqu'elle remplace * par tous les champs...

1) comme je dis à mes étudiants, normalement un schéma bien fait ne
nécessite pas de modifications en cours de route. Bon ok, en pratique ca
arrive, mais ca veut aussi peut-être dire que la modélisation initiale
n'était pas l'optimale.

2) au pire vous recréez la vue, je ne vois pas bien la difficulté.
De toute façon vos changements de schéma sont versionés dans un outil
type cvs ou git pour être à la mode, et vos changements déployés sous
forme de scripts .sql non ?

Mon rève serait une vue qui ne soit pas une vue mais plutot une macro.
c'est a dire que mon SELECT * (entre autre) resterai un SELECT *.

Regardez du côté des règles de re-écriture dans PostgreSQL:
CREATE RULE ...

Cela dit je pense quand même que je le problème se situe du côté des
changements incessants(?) de schéma...

--
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/>