Le planificateur ignore les index malgré tout

Mon avis est que le plan d'exécution obtenu déjà est optimal, il n'est pas possible de faire mieux en ajoutant une clef primaire ou un autre index.

Citation:

---

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.

---

C'est affligeant de constater toujours les mêmes erreurs !
Vous ne voulez pas respecter les règles de bonnes pratiques d'un SGBD relationnel en ne faisant justement aucune relation et vous demandez que cela soit performant.

Nous vous avons déjà dit et répété (MERCI LE CROSS POST !!!) que tant que votre modèle restait en l'état vous ne pourriez rien en attendre.
Ne seriez vous pas du genre à utiliser une formule 1 pour faire un déménagement et un camion pour participer à une course sur circuit ?

Un SGBD R (LE R de SGBDR signifie RELATIONNEL) est fait pour travailler avec des relations qui sont des objets mathématiques précis obéissant à une théorie bien connue. Les SGBDR sont optimisés pour traiter des relations... Pas des blobs, ni des fichiers, ni des structures de données informes.
La première des optimisations, c'est par le modèle de données qu'on l'obtient.

Tant que vous ne vous mettrez pas cela dans la tête ce thread de discussion est stérile...

Citation:

---

Envoyé par SQLpro

C'est affligeant de constater toujours les mêmes erreurs !
Vous ne voulez pas respecter les règles de bonnes pratiques d'un SGBD relationnel en ne faisant justement aucune relation et vous demandez que cela soit performant.

Nous vous avons déjà dit et répété (MERCI LE CROSS POST !!!) que tant que votre modèle restait en l'état vous ne pourriez rien en attendre.
Ne seriez vous pas du genre à utiliser une formule 1 pour faire un déménagement et un camion pour participer à une course sur circuit ?

Un SGBD R (LE R de SGBDR signifie RELATIONNEL) est fait pour travailler avec des relations qui sont des objets mathématiques précis obéissant à une théorie bien connue. Les SGBDR sont optimisés pour traiter des relations... Pas des blobs, ni des fichiers, ni des structures de données informes.
La première des optimisations, c'est par le modèle de données qu'on l'obtient.

Tant que vous ne vous mettrez pas cela dans la tête ce thread de discussion est stérile...

---

S'il vous plaît, je ne suis pas un expert de base de données comme vous, si je suis ici c'est que je n'ai pas trouvé de solution a mon problème, donc essayez d'être un peu plus explicite.
J'ai bien compris ce message avant mais je ne peux rien faire pour l'instant, et je suis entrain de penser a une solution donc si vous avez des idées je serai content, plutôt que lire que des critiques.
Un docteur est supposé de t'indiquer le médicament pour guérir, et pas seulement te dire c'est quoi ta maladie. Bien sur je ne vous demande pas de faire la conception a ma place, mais je n'ai rien retiré de vos commentaires.
Merci pour votre compréhension.

Voila le résultat de la reindex:

Code:

---

1 2 3 4 5 6

"Sort (cost=92790.86..92848.26 rows=22959 width=28) (actual time=206206.692..206271.624 rows=39521 loops=1)" " Sort Key: freq" " Sort Method: external merge Disk: 1680kB" " -> Index Scan using part3_idx on fourgrams (cost=0.00..90616.85 rows=22959 width=28) (actual time=177.896..205799.079 rows=39521 loops=1)" " Index Cond: (lower((part3)::text) = 'cheese'::text)" "Total runtime: 206316.554 ms"

---

Citation:

---

Envoyé par solicel

Voila le résultat de la reindex:

Code:

---

1 2 3 4 5 6

"Sort (cost=92790.86..92848.26 rows=22959 width=28) (actual time=206206.692..206271.624 rows=39521 loops=1)" " Sort Key: freq" " Sort Method: external merge Disk: 1680kB" " -> Index Scan using part3_idx on fourgrams (cost=0.00..90616.85 rows=22959 width=28) (actual time=177.896..205799.079 rows=39521 loops=1)" " Index Cond: (lower((part3)::text) = 'cheese'::text)" "Total runtime: 206316.554 ms"

---

---

206 secondes, OK, apparemment la réindexation ne fait rien gagner, encore qu'il faudrait chercher avec le même litéral que précédemment pour comparer les temps, c.a.d 'ham' et non pas 'cheese' car le nombre de lignes renvoyées diffère pas mal entre les deux.
je reste sur l'idée qu'il n'y a pas de différence entre les différents index y compris le premier et que la vitesse de certaines requêtes par rapport à d'autres est dû au fait que parfois les blocs disque sont déjà dans le cache disque.

Citation:

---

Envoyé par estofilo

encore qu'il faudrait chercher avec le même litéral que précédemment pour comparer les temps, c.a.d 'ham' et non pas 'cheese' car le nombre de lignes renvoyées diffère pas mal entre les deux.

---

C'est vrai mais quand j'exécute la même requête avec la colonne 1 c'est toujours en quelques millisecondes que j'obtiens le résultat.

Citation:

---

Envoyé par estofilo

je reste sur l'idée qu'il n'y a pas de différence entre les différents index y compris le premier et que la vitesse de certaines requêtes par rapport à d'autres est dû au fait que parfois les blocs disque sont déjà dans le cache disque.

---

Et dans ce cas concrètement que faut-il faire?
Et pourquoi ceci ne se passe pas avec la colonne 1?

Citation:

---

Envoyé par solicel

C'est vrai mais quand j'exécute la même requête avec la colonne 1 c'est toujours en quelques millisecondes que j'obtiens le résultat.

---

Je trouve ça assez surprenant surtout vu le nombre de résultats renvoyés, entre 20k et 40k.
Est-ce qu'il se peut que cet index sur la colonne1 ait des caractéristiques très différentes des autres, ou qu'il y ait beaucoup de vide dans cette colonne ou qqch du genre?
Peut-être que regarder les tailles des index donnerait une piste:

Code:

---

1 2 3 4 5

SELECT pg_size_pretty(pg_relation_size('part1_idx')), pg_size_pretty(pg_relation_size('part2_idx')), pg_size_pretty(pg_relation_size('part3_idx')), pg_size_pretty(pg_relation_size('part4_idx'));

---

Et sinon est-ce que les définitions des index sont rigoureusement identiques?
Pas de clauses de storage particulières ou tablespace différents? Est-ce qu'il s'agit de type btree dans tous les cas (faire un dump du schéma avec pg_dump -s pour retrouver éventuellement les CREATE INDEX).
Est-ce que la table a été créée directement avec toutes les colonnes ou bien est-ce que les 2,3,4 ont été ajoutées après?
Il y aussi la requête suivante qui peut donner des infos:

Code:

---

select indexrelname,idx_scan,idx_tup_read,idx_tup_fetch from pg_stat_user_indexes;

---

Citation:

---

S'il vous plaît, je ne suis pas un expert de base de données comme vous, si je suis ici c'est que je n'ai pas trouvé de solution a mon problème, donc essayez d'être un peu plus explicite.
J'ai bien compris ce message avant mais je ne peux rien faire pour l'instant, et je suis entrain de penser a une solution donc si vous avez des idées je serai content, plutôt que lire que des critiques.
Un docteur est supposé de t'indiquer le médicament pour guérir, et pas seulement te dire c'est quoi ta maladie. Bien sur je ne vous demande pas de faire la conception a ma place, mais je n'ai rien retiré de vos commentaires.
Merci pour votre compréhension.

---

Le médicament est simple ; apprendre à vous servir d'un SGBDR et apprendre la modélisation de données. Il y a des cours et des livres sur le sujet.

A +

Citation:

---

Envoyé par estofilo

Code:

---

1 2 3 4 5

SELECT pg_size_pretty(pg_relation_size('part1_idx')), pg_size_pretty(pg_relation_size('part2_idx')), pg_size_pretty(pg_relation_size('part3_idx')), pg_size_pretty(pg_relation_size('part4_idx'));

---

---

Voila le resultat:

Code:

---

"26 GB";"26 GB";"26 GB";"26 GB"

---

Citation:

---

Envoyé par estofilo

Et sinon est-ce que les définitions des index sont rigoureusement identiques?

---

oui

Citation:

---

Envoyé par estofilo

Pas de clauses de storage particulières ou tablespace différents?

---

Je ne pense pas (voir plus bas les commandes de création des indexes)

Citation:

---

Envoyé par estofilo

Est-ce qu'il s'agit de type btree dans tous les cas (faire un dump du schéma avec pg_dump -s pour retrouver éventuellement les CREATE INDEX).

---

Voila les commandes que j'ai utilisées:

Code:

---

1 2 3 4 5

CREATE INDEX part1_idx ON fourgrams USING btree (lower(part1)); CREATE INDEX part2_idx ON fourgrams USING btree (lower(part2)); CREATE INDEX part3_idx ON fourgrams USING btree (lower(part3)); CREATE INDEX part4_idx ON fourgrams USING btree (lower(part4));

---

Citation:

---

Envoyé par estofilo

Est-ce que la table a été créée directement avec toutes les colonnes ou bien est-ce que les 2,3,4 ont été ajoutées après?

---

La table a été créeé avec toutes les colonnes des le début, mais les indexes sont créés l'un apres l'autre en commencant par la colonne 1.

Citation:

---

Envoyé par estofilo

Il y aussi la requête suivante qui peut donner des infos:

Code:

---

select indexrelname,idx_scan,idx_tup_read,idx_tup_fetch from pg_stat_user_indexes;

---

---

Ca par contre ca montre une différence entre les colonnes:

Code:

---

1 2 3 4 5 6

relname idx_scan idx_tup_ read idx_tup_fetch part1_idx 96 50892498 6568055 part2_idx 92 6332078 1904234 part3_idx 60 39407053 11838481 part4_idx 35 1754708 474258

---

Hélas, ces infos ne semblent apporter aucun indice particulier sur la différence de rapidité d'accès au premier index par rapport aux autres.

Citation:

---

Envoyé par estofilo

Hélas, ces infos ne semblent apporter aucun indice particulier sur la différence de rapidité d'accès au premier index par rapport aux autres.

---

Merci beaucoup pour vos efforts, je vais continuer a essayer, je suis sur qu'il y a une solution.

Citation:

---

Envoyé par 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 ...)

---

Tu t'entêtes pour rien là ... Relis mon précédent post ou ceux de SQLPro ...
En informatique il n'y a pas toujours de recette miracle surtout quand il faut pallier à des problèmes initiaux de conception ...
Un SGBD relationnel ce n'est pas fait pour tout mettre dans une seule table afin de remplacer Excel quand on rencontre la limitation à 65 mille lignes ...