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.