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