Re: [pgsql-fr-generale] BDD PostgreSQL 8.0.0 de 200 GO - Retour d'expérience sur les temps de réponse

En quelques mots, notre retour d'expérience sur notre BDD PostgreSQL 8.0.0
de 200 GO pour améliorer les performances en lecture :

1. Deux index créés sur une même table ne sont pas utilisable en même temps.
Voir à ce propos le dernier paragraphe de cette URL :
http://traduc.postgresqlfr.org/pgsql-fr/indexes.html. Par exemple, pour
profiter des index sur la colonne col_a de la table table_1, mais aussi de
l'index col_b de la table table_1 (et améliorer de ce fait les temps de
réponse d'un facteur de 1100 à 1), il faut transformer cette simple requête
:

SELECT col_a, col_b, col_c, col_d
FROM table_1 a
WHERE a.col_a = 'TOTO'
AND a.col_b = 'TRUC'
;

en cette requête illisible, qui "passe" d'ailleurs sur d'autres SGBDRs :

SELECT col_a, col_b, col_c, col_d
FROM table_1 a
WHERE a.col_a = 'TOTO'
AND a.col_b = 'TRUC'
AND EXISTS (
SELECT b.col_a, b.col_b FROM table_1 b
WHERE a.col_a = b.col_a
AND a.col_b = b.col_b
)
;

voire en cette requête (auto-jointure), qui ne "passe" pas sur d'autres
SGBDRs :

SELECT col_a, col_b, col_c, col_d
FROM table_1 a INNER JOIN table_1 b
USING (col_a, col_b, col_c, ..., col_x)
WHERE a.col_a = 'TOTO'
AND a.col_b = 'TRUC'
;

2. Pour pallier au problème précédent, il aurait fallu créer un index
composite de type CREATE INDEX idx_a_b ON table_1 (col_a, col_b). Bien
entendu, sa création améliore aussi les temps de réponse d'un facteur de
1100 à 1. Néanmoins, nous sommes dans l'impossibilité de créer un "bon"
index composite pour chaque requête qui "en aurait besoin", la combinatoire
étant exponentielle. Surtout pour des bases de données qui serait de type
datawarehouse.

3. L'ordre des colonnes spécifiées dans une clause WHERE n'a, heureusement,
pas d'impact.

4. Nous avons remarqué que les "bons" index créés ne sont pas toujours
utilisés pour des clauses WHERE col_a LIKE 'TOTO%' et même WHERE col_a =
'TOTO'. Ce problème semble se présenter lorsque la base de données n'a pas
été créée en localisation C mais en localisation fr_Fr. Lire à ce propos le
chapitre "Si vous utilisez la localisation C, vous pouvez à la place créer
un index avec la classe d'opérateurs par défaut, qui sera utilisable pour
les recherches de motifs. Notez aussi qu'il faut créer un index avec la
classe d'opérateurs par défaut si vous voulez que les requêtes qui utilisent
une comparaison ordinaire utilisent un index. De telles requêtes ne peuvent
pas utiliser les classes d'opérateurs xxx_pattern_ops. Il est possible de
créer plusieurs index sur la même colonne avec différentes classes
d'opérateurs." de l'URL :
http://traduc.postgresqlfr.org/pgsql-fr/indexes-opclass.html.

En clair, pour une colonne col_a de type VARCHAR de la table table_1, on ne
passe par le "bon" 'index pour les clauses WHERE col_a LIKE 'TOTO%', voire
WHERE col_a = 'TOTO' que si cet index est créé avec l'ordre SQL CREATE INDEX
idx_col_a ON table_1 (col_a VARCHAR_PATTERN_OPS). Attention, si ce "bon"
index n'est toujours pas utilisé pour la clause WHERE col_a = 'TOTO', il
faut reprendre le SQL par la réécriture de la clause en WHERE col_a LIKE
'TOTO'. Peut donc être impactée cette requête :

SELECT col_a, col_b, col_c, col_d
FROM table_1 a
WHERE a.col_a LIKE 'TOTO%'
AND a.col_b = 'TRUC'
;

voire celle-ci :

SELECT col_a, col_b, col_c, col_d
FROM table_1 a
WHERE a.col_a = 'TOTO'
AND a.col_b = 'TRUC'
;

que l'on transformera alors en :

SELECT col_a, col_b, col_c, col_d
FROM table_1 a
WHERE a.col_a LIKE 'TOTO'
AND a.col_b = 'TRUC'
;

5. Comme à l'habitude sur les SGBDRs, les temps de réponse de nos requêtes
dépendent presqu'exclusivement de la réécriture du SQL et non pas de la
modification des cinq ou six malheureux paramètres du fichier
postgresql.conf. Nous confirmons quand même les conseils donnés ici et là
sur le Net : un "petit" shared_buffers, un "très grand"
effective_cache_size, un random_page_cost à 2.

Bien Cordialement.

-------------------------------------
Pierre-André ROELTGEN
MINISTERE DE L'INTERIEUR, DE LA
SECURITE INTERIEURE ET DES LIBERTES LOCALES
SG/DSIC/SCSIC/DESP/BSTN
-------------------------------------

ROELTGEN Pierre-Andre DSIC DESP a écrit :
> En quelques mots, notre retour d'expérience sur notre BDD PostgreSQL
> 8.0.0 de 200 GO pour améliorer les performances en lecture :
>
> 1. Deux index créés sur une même table ne sont pas utilisable en même
> temps. Voir à ce propos le dernier paragraphe de cette URL :
> http://traduc.postgresqlfr.org/pgsql-fr/indexes.html. Par exemple, pour
> profiter des index sur la colonne col_a de la table table_1, mais aussi
> de l'index col_b de la table table_1 (et améliorer de ce fait les temps
> de réponse d'un facteur de 1100 à 1), il faut transformer cette simple
> requête :

Bien!

Vos tests nous seront à n'en pas douter d'une grande utilité à nombre de
personnes sur la liste, ils donnent un bon moyen de contournement...

Par contre, ayez à l'esprit que PostgreSQL est un SGBD "vivant" de part
sa communauté...

Pourquoi vous dis-je cela? Parceque le problème que vous avez rencontré
est dû à un problème connu de PG: il s'agit de l'absence d'index de type
BITMAP. (à la Oracle, mais pas seulement).

Le support des BITMAP index est dans la TODO List depuis un bon moment déjà:

http://developer.postgresql.org/todo.php

Dans cette page, veuillez lire le détail des deux items suivants:

"Allow non-bitmap indexes to be combined by creating bitmaps in memory

Allow the creation of on-disk bitmap indexes which can be quickly
combined with other bitmap indexes"

...vous remarquerez que cela correspond pile poil à votre problème!

Alors je me suis renseigné auprès des développeurs de PG. Il se trouve
que cela n'était pas dans leur priorité, ils voulaient d'abord finir PG
8.0 avec toutes les nouveautés "fortes" que cette version apporte.

La bonne nouvelle c'est qu'il prévoient la fonctionalité pour la version
8.1, dont le développement prendra peut être 2 à 4 mois. (à moduler avec
le fameux "release when ready")

En conclusion, faites attention à ne pas conclure trop vite dans votre
étude que PG "n'est pas capable de..." avec les logiciels libre, tout
est affaire de temps.

Merci de votre intérêt pour PostgreSQL !

--
Jean-Paul Argudo
www.PostgreSQLFr.org