Discussion :

[solicel]

Bonjour,
S'il vous plaît aidez moi, car cela fait des mois que nous travaillons sur cette application, et maintenant elle risque d'être inutile.
Voici d'abord une description de l'application:
Nous voulons interroger des bases de données contenant un très grand volume de données, mais très peu de tables (une table par base, 4 bases en total) avec des requetes simples (select * from..... where ... order by..) avec la clause where pouvant contenir au maximun 5 individus (le nombre maximum des colonnes), mais en général une ou deux. Donc il n'y a pas de jointure.
La structure de table aussi est simple, ne contient pas de clé primaire.
La difficulté donc provient de la taille des données (environ 300 GO sans les indexes) ce qui consomme énormément de temps.
Nous avons construit une indexe pour chaque colonne, mais un truc bizarre se passe: lorsque nous faisons une requête la ou on mentionne un élément de la première colonne, il répond très vite. Par contre si on essaye de travailler avec les autres indexes, il prend énormément de temps. J'ai essayé de forcer l'usage des indexes avec SET ENABLE_SEQSCAN=OFF; mais en vain. Après avoir fouillé un peu, j'ai essayé EXPLAIN ANALYZE, qui donne les résultats suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
explain analyze select * from table where lower(part1)='cheese' order by freq;
"Sort  (cost=2282819.01..2284644.59 rows=730232 width=28) (actual time=223.852..254.722 rows=30220 loops=1)"
"  Sort Key: freq"
"  Sort Method:  external merge  Disk: 1272kB"
"  ->  Index Scan using part1_idx on fourgrams  (cost=0.00..2179249.73 rows=730232 width=28) (actual time=0.146..104.445 rows=30220 loops=1)"
"        Index Cond: (lower((part1)::text) = 'cheese'::text)"
"Total runtime: 267.728 ms"
 
____________________________________________________
select * from table where lower(part2)='cheese' order by freq;
"Sort  (cost=1665821.33..1667039.19 rows=487146 width=28) (actual time=41718.824..41763.119 rows=37135 loops=1)"
"  Sort Key: freq"
"  Sort Method:  external merge  Disk: 1568kB"
"  ->  Bitmap Heap Scan on fourgrams  (cost=8976.12..1598149.66 rows=487146 width=28) (actual time=152.461..41592.935 rows=37135 loops=1)"
"        Recheck Cond: (lower((part2)::text) = 'cheese'::text)"
"        ->  Bitmap Index Scan on part2_idx  (cost=0.00..8854.33 rows=487146 width=0) (actual time=124.171..124.171 rows=37135 loops=1)"
"              Index Cond: (lower((part2)::text) = 'cheese'::text)"
"Total runtime: 41779.212 ms"
 
 
_____________________________________________________
explain analyze select * from table where lower(part3)='cheese' order by freq;
"Sort  (cost=1385290.57..1386281.11 rows=396215 width=28) (actual time=148020.223..148066.516 rows=39521 loops=1)"
"  Sort Key: freq"
"  Sort Method:  external merge  Disk: 1672kB"
"  ->  Bitmap Heap Scan on fourgrams  (cost=7319.24..1330838.65 rows=396215 width=28) (actual time=63.751..147810.306 rows=39521 loops=1)"
"        Recheck Cond: (lower((part3)::text) = 'cheese'::text)"
"        ->  Bitmap Index Scan on part3_idx  (cost=0.00..7220.19 rows=396215 width=0) (actual time=26.770..26.770 rows=39521 loops=1)"
"              Index Cond: (lower((part3)::text) = 'cheese'::text)"
"Total runtime: 148091.664 ms"

Vous remarquez bien ici que le planificateur utilise l'index pour la premiere colonne, mais pour les autres cas il fait le calcul, puis fait le "fetch" que je ne comprend pas.
NB: - la colonne freq qui sert a ordonner n'est pas indexée
- les index sont construit sur le "lower" des colonnes
Donc que faire pour que le planificateur utilise les index de toutes les colonnes?

Merci Beaucoup pour votre temps.
Très cordialement.

[estofilo]

Deux suggestions
1) booster les statistiques pour les colonnes qui posent problème, ça donnera à l'optimiseur une meilleure estimation

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
ALTER TABLE la_table ALTER column col SET STATISTICS 100;

(la valeur par défaut est 10 je crois)
Refaire ANALYZE de la table et réessayer les requêtes. Eventuellement monter encore plus la valeur

2) alternativement, forçer l'optimiseur à ne pas utiliser de bitmapscan puisqu'apparemment c'est ça qui diffère entre la requête rapide et la lente

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
set enable_bitmapscan to false;

[solicel]

Merci pour votre réponse intéressante.
Mais cela n'a rien changé.
J'ai essayé les valeurs 100, 300 et 1000 (la valeur maximale).
J'ai également fait la commande pour désactiver le bitmapscan mais en vain.
J'ai même modifié le fichier postgresql.conf, en désactivant le seqscan, le bitmapscan, et le nestloop.
A l'aide!

[scheu]

As-tu au moins recalculé les stats de ta table après avoir fait le "SET STATISTICS 100;"
Ca vient peut-être du pourcentage de valeurs qui valent 'cheese' qui n'est pas le même selon la colonne que tu attaques
Combien de lignes au total dans ta table ?
Combien de lignes valent 'cheese' en fonction de la colonne que tu filtres ? Est-ce le même nombre ?
Si tu dois ramener 80% de tes lignes, Postgresql trouves peut-être plus judicieux (ou en tout cas moins long) de ne pas utiliser l'index de la même manière ...

[solicel]

As-tu au moins recalculé les stats de ta table après avoir fait le "SET STATISTICS 100;"
Ca vient peut-être du pourcentage de valeurs qui valent 'cheese' qui n'est pas le même selon la colonne que tu attaques
Combien de lignes au total dans ta table ?
Combien de lignes valent 'cheese' en fonction de la colonne que tu filtres ? Est-ce le même nombre ?
Si tu dois ramener 80% de tes lignes, Postgresql trouves peut-être plus judicieux (ou en tout cas moins long) de ne pas utiliser l'index de la même manière ...

Le nmbre de lignes est de l'ordre de : 1 314 150 000 lignes.
Le plus grand nombre de lignes que j'ai reçu jusqu'à l'instant est de l'ordre de 900 000.
'Cheese' n'est qu'un simple exemple, mais les mots clé sont insérés par l'utilisateur a chaque fois dans la colonne qu'il choisit. C'est pour cela l'interrogation des indexes est équiprobable.

[solicel]

Que pensez vous de ca?

Les estimations de coût du planificateur ne sont pas linéaires et, du coup, il pourrait bien choisir un plan différent pour une table plus petite ou plus grande. Un exemple extrême est celui d'une table occupant une page disque. Vous obtiendrez pratiquement toujours un parcours séquentiel que des index soient disponibles ou non. Le planificateur réalise que cela va nécessiter la lecture d'une seule page disque pour traiter la table dans ce cas, il n'y a donc pas d'intérêt à étendre des lectures de pages supplémentaires pour un index.

[estofilo]

Dans l'explain analyze posté initialement, celui-là:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SELECT * FROM TABLE WHERE lower(part2)='cheese' ORDER BY freq;
"Sort  (cost=1665821.33..1667039.19 rows=487146 width=28) (actual time=41718.824..41763.119 rows=37135 loops=1)"
"  Sort Key: freq"
"  Sort Method:  external merge  Disk: 1568kB"
"  ->  Bitmap Heap Scan on fourgrams  (cost=8976.12..1598149.66 rows=487146 width=28) (actual time=152.461..41592.935 rows=37135 loops=1)"
"        Recheck Cond: (lower((part2)::text) = 'cheese'::text)"
"        ->  Bitmap Index Scan on part2_idx  (cost=0.00..8854.33 rows=487146 width=0) (actual time=124.171..124.171 rows=37135 loops=1)"
"              Index Cond: (lower((part2)::text) = 'cheese'::text)"
"Total runtime: 41779.212 ms"

ça dit que le nombre de lignes estimé avec les stats est de 487146 et que le nombre réel est 37135 (donc déjà grosse différence entre estimation et réalité).
Etant donné que c'est un index fonctionnel, il est possible que le fait de booster les stats via set statistics ne procure pas un avantage aussi marquant que si c'était un index simple.
Ce serait à vérifier en comparant avec le nouvel explain analyze après les modifs.

D'autre part, en cas de SET enable_bitmapscan TO false;
je suis quasiment certain qu'il changera de plan d'exécution.
Là aussi il serait utile d'avoir le résultat d'explain analyze pour voir ce qu'il en est.

[estofilo]

Que pensez vous de ca?

Les estimations de coût du planificateur ne sont pas linéaires et, du coup, il pourrait bien choisir un plan différent pour une table plus petite ou plus grande. Un exemple extrême est celui d'une table occupant une page disque. Vous obtiendrez pratiquement toujours un parcours séquentiel que des index soient disponibles ou non. Le planificateur réalise que cela va nécessiter la lecture d'une seule page disque pour traiter la table dans ce cas, il n'y a donc pas d'intérêt à étendre des lectures de pages supplémentaires pour un index.

Que tu n'es pas du tout dans ce cas de figure puisque ta table est énorme (au-dessus d'un milliard de lignes si j'ai bien lu). De plus l'index est effectivement utilisé dans les explain analyze que tu as posté, mais il l'est de 2 manières différentes:
- via un bitmap index scan, et il s'avère que c'est trop lent
- via un index scan simple, et là par contre c'est rapide

le but me semble-t-il est de forcer un peu la main à PG pour qu'il utilise la méthode rapide à chaque fois

[solicel]

Oui! Mais comment?
Entre autres, je ne savais pas que le bitmap index scan est différent du l'index scan simple.
J'avoue que mes connaissances en base de données sont très limités..
Mais aussi cela m'étonnes que postgres se comporte de cette manière vu la réputation que j'ai entendu parler..

[solicel]

Je ramène les nouvelles resultats de explain analyze dans quelques minutes, comme vous savez, ça prend du temps!

[solicel]

Cette fois il y a du nouveau.
Le planificateur utilise les indexes! Mais il n'y a pas d'amélioration nette au niveau des performances.
Voici les explains:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
explain analyze select * from fourgrams where lower(part1)='ham' order by freq desc;
"Sort  (cost=85162.05..85234.83 rows=29114 width=28) (actual time=513.470..524.790 rows=12068 loops=1)"
"  Sort Key: freq"
"  Sort Method:  external merge  Disk: 472kB"
"  ->  Index Scan using part1_idx on fourgrams  (cost=0.00..82355.83 rows=29114 width=28) (actual time=209.789..416.256 rows=12068 loops=1)"
"        Index Cond: (lower((part1)::text) = 'ham'::text)"
"Total runtime: 530.030 ms"
______________________________________________________________________________
explain analyze select * from fourgrams where lower(part2)='ham' order by freq desc; 
"Sort  (cost=96377.12..96437.69 rows=24228 width=28) (actual time=34260.980..34272.327 rows=13579 loops=1)"
"  Sort Key: freq"
"  Sort Method:  external merge  Disk: 520kB"
"  ->  Bitmap Heap Scan on fourgrams  (cost=576.50..94073.79 rows=24228 width=28) (actual time=214.265..34220.593 rows=13579 loops=1)"
"        Recheck Cond: (lower((part2)::text) = 'ham'::text)"
"        ->  Bitmap Index Scan on part2_idx  (cost=0.00..570.45 rows=24228 width=0) (actual time=165.948..165.948 rows=13579 loops=1)"
"              Index Cond: (lower((part2)::text) = 'ham'::text)"
"Total runtime: 34278.382 ms"
 
-- Apres SET enable_bitmapscan TO false;
explain analyze select * from fourgrams where lower(part2)='ham' order by freq desc; 
"Sort  (cost=98938.70..98999.27 rows=24228 width=28) (actual time=72.001..84.559 rows=13579 loops=1)"
"  Sort Key: freq"
"  Sort Method:  external merge  Disk: 520kB"
"  ->  Index Scan using part2_idx on fourgrams  (cost=0.00..96635.37 rows=24228 width=28) (actual time=0.053..23.918 rows=13579 loops=1)"
"        Index Cond: (lower((part2)::text) = 'ham'::text)"
"Total runtime: 105.349 ms"
__________________________________________________
explain analyze select * from fourgrams where lower(part3)='ham' order by freq desc; 
"Sort  (cost=92822.97..92880.39 rows=22967 width=28) (actual time=97508.865..97527.083 rows=13805 loops=1)"
"  Sort Key: freq"
"  Sort Method:  external merge  Disk: 528kB"
"  ->  Index Scan using part3_idx on fourgrams  (cost=0.00..90648.32 rows=22967 width=28) (actual time=200.617..97452.604 rows=13805 loops=1)"
"        Index Cond: (lower((part3)::text) = 'ham'::text)"
"Total runtime: 97533.131 ms"
_____________________________________________________
explain analyze select * from fourgrams where lower(part4)='ham' order by freq desc; 
"Sort  (cost=99331.26..99391.83 rows=24225 width=28) (actual time=102213.560..102230.764 rows=11391 loops=1)"
"  Sort Key: freq"
"  Sort Method:  external merge  Disk: 448kB"
"  ->  Index Scan using part4_idx on fourgrams  (cost=0.00..97028.17 rows=24225 width=28) (actual time=191.458..102162.929 rows=11391 loops=1)"
"        Index Cond: (lower((part4)::text) = 'ham'::text)"
"Total runtime: 102237.412 ms"

Je suis désolé de ne pas avoir remarqué ça, car suis sur d'avoir mettre le SET enable_bitmapscan TO false; mais peut être parce que c'est volatile c'est pour cela qu'il n'a pas persisté dans la configuration.
L'important ici est que:
- Je pense l'écart existe encore entre le nombre des lignes estimées et réelles
- l'utilisation des indexes n'a pas permis de retrouver les résultats rapidement (5 minutes en général, parfois en 1 minute) contre 5 secondes en moyenne pour la colonne 1. Et pour cela que je n'ai remarqué la différence quand j'ai désactivé les bitmapscan.
Les stats sont tous mis a 1000 (y inclus la colonne 1, et la colonne fréquence qui n'est pas indexée).
Je pense le jeu vient juste de commencer.. Merci pour votre participation!

[scheu]

Au risque de te décevoir, ton modèle de données est mal pensé ...
Avoir une table de 1.3 milliards de lignes, forcément tes temps de réponse sur des requêtes qui ne te ramènent "que" 900 000 ligne seront longs, même avec des indexes

Pour des volumétries pareilles, il faudrait utiliser le partitionnement sous Postgresql (grâce à l'héritage de tables)

Ou alors t'arranger pour organiser tes données différemment avec plus de tables, des clés étrangères, ... bref refaire ton modèle

Ou alors utiliser un SGBD mieux adapté pour de telles volumétries comme Oracle (partitionnement, sous partitionnement, parallélisme, compression de données, etc ...)

[solicel]

C'est quoi le partitionnement? Pouvez vous m'expliquer un peu plus?
Je pense que c'est intéressant dans le cas ou ou je vais subdiviser la table en 30 par exemple, classé par l'ordre alphabétique des composants de la première colonne (notre corpus est déjà partitionné dans fichiers dans cet ordre), mais le problème si la requête ne touche pas la première colonne, dans ce cas (qui est très probable) on sera obligé de faire l'union de toutes les tables et faire le tri après.
L'application n'a pas vraiment besoin de conception dans l'esprit de la base de donnée relationnelle, c'est un problème technique pour la manipulation d'un grand volume de données dans une perspective de fouille de données, c'est pour cela que la conception parait si grossière.

[estofilo]

Effectivement le SET enable_bitmapscan TO false; n'est valable que dans la session en cours, c'est normal.
On voit sur les résultats, qu'avec ce paramètre à false, les requêtes sur part1 et part2 sont rapides et celles sur part3 et part4 sont toujours lentes bien qu'elles suivent un bon plan d'exécution.
Si on regarde uniquement le temps pris pour faire les index scan, on voit ça:

" -> Index Scan using part2_idx on fourgrams (cost=0.00..96635.37 rows=24228 width=28) (actual time=0.053..23.918 rows=13579 loops=1)"
" -> Index Scan using part3_idx on fourgrams (cost=0.00..90648.32 rows=22967 width=28) (actual time=200.617..97452.604 rows=13805 loops=1)"
" -> Index Scan using part4_idx on fourgrams (cost=0.00..97028.17 rows=24225 width=28) (actual time=191.458..102162.929 rows=11391 loops=1)"

Donc pour ramener à peu près le même nbre de lignes avec la même méthode d'accès, ça prend 23ms pour part2_idx mais 97s et 100s pour les 2 autres.

A ce niveau là ce n'est pas un pb de stats ou de choix de plan d'exécution puisque ce sont les mêmes, ce serait plutôt un pb de rapidité d'accès aux données sur le disque.

Moi j'essaierais deux choses: d'abord réexécuter plusieurs fois la même requête avec l'explain analyze pour voir si les temps d'exécution sont constants ou bien si ce n'est que la 1ere exécution qui est lente (l'effet d'accélération à partir de la 2eme exécution est dû à la mise en cache des blocs disque). Si la 2eme exécution est beaucoup plus rapide, ça valide l'hypothèse des accès disque trop lents.

Et éventuellement tenter un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

REINDEX index part3_idx;

et voir si ça fait une différence avec la requête sur le champ part3, des fois que l'index soit complètement éparpillé sur le disque.Si c'est mieux après, le faire aussi biensûr sur part4_idx. Attention, sur un gros index ça prend biensûr beaucoup de temps pendant lequel on ne peut pas écrire dans la table.

[solicel]

Merci beaucoup pour tes analyses, ça me rassure bien..
Quant a la répétition des requêtes c'est vrai, la deuxième/troisième fois est nettement plus rapide, j'ai remarqué ça depuis longtemps.
Dans ce cas dois-je augmenter la cache ou quoi?
J'ai essayé d'augmenter la mémoire partagée parce que postgres l'utilise comme buffer, mais je ne sais pas s'il s'agit de la même chose.
Par la meme occasion, mon serveur n'est pas très puissant en terme de matériel:
Pentium 4 a 3Ghz, 1TO disque dur, 1 Giga de ram que j'essaye de pallier par 6 Giga de swap.
J'attend les commentaires...

[solicel]

Et éventuellement tenter un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

REINDEX index part3_idx;

et voir si ça fait une différence avec la requête sur le champ part3, des fois que l'index soit complètement éparpillé sur le disque.Si c'est mieux après, le faire aussi biensûr sur part4_idx. Attention, sur un gros index ça prend biensûr beaucoup de temps pendant lequel on ne peut pas écrire dans la table.

Je fais cela juste avant de quitter, pour le laisser faire cela toute la nuit.

[estofilo]

Merci beaucoup pour tes analyses, ça me rassure bien..
Quant a la répétition des requêtes c'est vrai, la deuxième/troisième fois est nettement plus rapide, j'ai remarqué ça depuis longtemps.
Dans ce cas dois-je augmenter la cache ou quoi?
J'ai essayé d'augmenter la mémoire partagée parce que postgres l'utilise comme buffer, mais je ne sais pas s'il s'agit de la même chose.
Par la meme occasion, mon serveur n'est pas très puissant en terme de matériel:
Pentium 4 a 3Ghz, 1TO disque dur, 1 Giga de ram que j'essaye de pallier par 6 Giga de swap.
J'attend les commentaires...

Si les données requêtées sont constamment différentes, augmenter le cache n'apportera probablement rien (d'ailleurs avec 1Go de mémoire seulement il n'y a pas tellement de marge de manoeuvre).
Par contre il peut y avoir un gain en réindexant comme je le disais dans un message au-dessus.
Au niveau matériel, les gros serveurs de BDD ont souvent beaucoup de disques en parallèle, par exemple une baie de 16 disques. Si au contraire il n'y a qu'un seul disque dur, dès que la tête du disque doit se balader pour aller chercher des données éloignées les unes des autres, la latence augmente énormément.

[solicel]

J'ai déjà commencé la reindex que vous avez proposée. La commande tourne depuis hier.
Entre temps, je me suis mis a penser et j'ai constaté un truc:
Si vous examinez bien les temps d'exécutions, vous allez trouver que le temps dépensé pour scanner les indexes est le même:
Index Scan using part1_idx on fourgrams (cost=0.00..82355.83 rows=29114 width=28) (actual time=209.789..416.256 rows=12068 loops=1)
Index Scan using part2_idx on fourgrams (cost=0.00..96635.37 rows=24228 width=28) (actual time=0.053..23.918 rows=13579 loops=1)
Index Scan using part3_idx on fourgrams (cost=0.00..90648.32 rows=22967 width=28) (actual time=200.617..97452.604 rows=13805 loops=1)
Index Scan using part4_idx on fourgrams (cost=0.00..97028.17 rows=24225 width=28) (actual time=191.458..102162.929 rows=11391 loops=1)

Ici pour la colonne 2, n'oubliez pas que j'ai fait la requête deux fois, et ce temps est après avoir désactiver le bitmapscan, mais d'habitude ça prend aussi presque 200 comme les autres, j'ai testé ça plus tard.

Par contre ce qui est intéressant, c'est les tris, regardons ça:
colonne 1: Sort (cost=85162.05..85234.83 rows=29114 width=28) (actual time=513.470..524.790 rows=12068 loops=1)"

Colonne 2: Sort (cost=98938.70..98999.27 rows=24228 width=28) (actual time=72.001..84.559 rows=13579 loops=1)"

Colonne 3: Sort (cost=92822.97..92880.39 rows=22967 width=28) (actual time=97508.865..97527.083 rows=13805 loops=1)"

Colonne 4: Sort (cost=99331.26..99391.83 rows=24225 width=28) (actual time=102213.560..102230.764 rows=11391 loops=1)"

La aussi l'avantage est donné pour la colonne 2 parce que la requête a été répétée. Donc le nouveau problème est encore dans le tri.
J'ai une idée dans la tête mais je ne sais pas si elle est correcte. Peut être postgres privilégie la première colonne car il la traite comme "pseudo" clé primaire, et c'est pour ça que tout va très rapidement avec elle. Serait-il sage de rajouter une clé primaire a la table, a fin de donner une vision plus claire au planificateur des requêtes?
Si c'est le cas j'ai deux alternatives a mon sens:
- utiliser la combinaison (part1, part2, part3, part4) comme clé primaire
- ajouter une nouvelle colonne pour ce faire
Quelle solution est plus adaptée?

[estofilo]

J'ai déjà commencé la reindex que vous avez proposée. La commande tourne depuis hier.
Entre temps, je me suis mis a penser et j'ai constaté un truc:
Si vous examinez bien les temps d'exécutions, vous allez trouver que le temps dépensé pour scanner les indexes est le même:
Index Scan using part1_idx on fourgrams (cost=0.00..82355.83 rows=29114 width=28) (actual time=209.789..416.256 rows=12068 loops=1)
Index Scan using part2_idx on fourgrams (cost=0.00..96635.37 rows=24228 width=28) (actual time=0.053..23.918 rows=13579 loops=1)
Index Scan using part3_idx on fourgrams (cost=0.00..90648.32 rows=22967 width=28) (actual time=200.617..97452.604 rows=13805 loops=1)
Index Scan using part4_idx on fourgrams (cost=0.00..97028.17 rows=24225 width=28) (actual time=191.458..102162.929 rows=11391 loops=1)

Non parce que le temps pris est la différence entre les 2 valeurs derrière time.
En fait c'est début..fin
Par exemple pour time=191.458..102162.929, le temps pris est 102162.929-191.458 = 101971.471 (millisecondes)

[solicel]

Non parce que le temps pris est la différence entre les 2 valeurs derrière time.
En fait c'est début..fin
Par exemple pour time=191.458..102162.929, le temps pris est 102162.929-191.458 = 101971.471 (millisecondes)

Ah oui, c'est bien j'apprends beaucoup de choses la.
Et par rapport a la clé primaire, c'est utile ou pas?