Discussion :

[SQLpro]

Donc selon toi les indexes couvrant plusieurs colonnes, comme ici, sont inefficaces?

Selon le type d'algorithme de rangement et recherche ils sont plus ou moins efficace.

Les index les plus classiques sont de type "row store", notamment le rangement est un tri et l'algorithme de recherche associé est un arbre équilibré.
Dans ce type d'organisation, un index a clef composite contenant par exemple les colonnes A, B, C (dans ce sens... en fait ce sont des vecteurs...)

Code :

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

CREATE INDEX X ON T (A, B, C)

Sera tres efficace pour les recherches suivantes :

Code :

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

1
2
3
4
5
6
7
8
WHERE A = ...
WHERE A = ... AND B = ...
WHERE A = ... AND B = ...
WHERE A = ... AND B = ... AND C = ...
WHERE A = ... AND B > ...
WHERE A = ... AND B < ...
WHERE A = ... AND B = ... AND C > ...
WHERE A = ... AND B = ... AND C < ...

Il sera TOTALEMENT inefficace pour les recherches suivantes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
WHERE B = ...
WHERE B = ... 
WHERE C = ...
WHERE B = ... AND C = ...
WHERE A = ... OR B = ...
WHERE A = ... OR C = ...
WHERE A = ... OR B = ... OR C = ...
WHERE A = ... OR B > ...
WHERE A = ... OR B < ...
WHERE A = ... OR B = ... OR C > ...
WHERE A = ... OR B = ... OR C < ...
...

À contrario un index "columstore" sera efficace pour ces recherches :

Code :

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

1
2
3
4
5
6
7
8
9
WHERE A = ... AND B = ...
WHERE A = ... AND B = ...
WHERE A = ... AND B = ... AND C = ...
WHERE C = ...
WHERE B = ... AND C = ...
WHERE A = ...
WHERE A = ... OR B = ...
WHERE A = ... OR B = ...
WHERE A = ... OR B = ... OR C = ...

mais pas pour :

Code :

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

1
2
3
4
5
6
7
8
WHERE A = ... AND B > ...
WHERE A = ... AND B < ...
WHERE A = ... AND B = ... AND C > ...
WHERE A = ... AND B = ... AND C < ...
WHERE A = ... OR B > ...
WHERE A = ... OR B < ...
WHERE A = ... OR B = ... OR C > ...
WHERE A = ... OR B = ... OR C < ...

Enfin pour les objets composites ayant des sous attributs (cas des arborescence) aucun index connu n'est efficace.

A +

[SQLpro]

...Pourtant je vois un moyen très simple de le faire, c'est de créer un index fonction.

Les index sur fonction, ou sur colonne calculées (équivalent dans SQL Server) sont utilisable de façon extrêmement limitées.
Un petit exemple est donnée par PostGreSQL qui ne sait toujours pas indexer du XML, mais propose d'indexer certaines portions du XML par le biais de fonctions... Il faudrait donc que je créé autant d'index sur fonction qu'il y a de balises et d'attributs XML...

• c'est totalement utopique pour des documents contenant un grand nombre de balise
• cela ne règle pas le problème de document arrivant avec de nouvelles balises et/ou attributs
• c'est infaisable avec des documents contenant des balises de nom identique mais figurant à un niveau hiérarchique différent...

Pourtant SQL Server propose une indexation générique des colonnes XML... Et c'est plutôt bluffant à l'usage !

Si je considère l'accès au champ x.y.z comme une fonction, alors cette fonction est déterministe et donc éligible à l'indexation.

Cela ne servira que pour quelques très petits cas et en pratique cela n'a aucun intérêt ! C'est comme les index filtrés, rares sont les cas réel d'usage...

Au passage, les SGBD relationnels ont déjà des attributs hiérarchiques, par exemple les RECORD en Oracle, qui peuvent eux-même contenir d'autres RECORD, ou l'usage de REF. Et il y a aussi l'héritage de type. Mais peut-être qu'on ne peut pas les indexer?

Effectivement impossible d'indexer ce type de données, comme il est impossible d'index les ARRAY de PG...

Pas besoin que le type soit scalaire pour ça, il suffit qu'il ait une relation d'ordre total correctement définie.
En programmation on crée souvent des arbres de recherche en mémoire, sans forcément y penser (par exemple, les types TreeMap et TreeSet en Java). Si tu regardes la spécification de ces types, tu verras qu'on n'impose pas que la clé soit scalaire, on impose seulement qu'elle ait une relation d'ordre valide.

Trouve moi la relation d'ordre valide d'un point géographique ayant deux coordonnées X et Y ?
Si tu défini l'ordre sur X, cela ne permet pas la recherche en X/Y, et inversement.
D'autres techniques d'indexation ont été définies qui ne prennent pas en compte l'ordre, mais le rangement (notion fondamentalement différentes...) Exemple : je peu trier mes vêtements, par longueur ou par largeur, mais pas les deux ! En revanche, je peut ranger mes vêtements dans un espace bi dimensionnel (le plan euclidien) en mettant sur l'axe des X la longueur et sur l'axe des Y la largeur.
Pour la recherche, je vais alors pouvoir morceler cet espace sous forme d'un pavage (tesselation) et donc procéder par élimination des "mailles" trop grande et trop petite.

C'est ainsi que fonctionne l'indexation des coordonnées spatiales....
https://books.google.fr/books?hl=fr&...page&q&f=false

Et quand bien même, comme tu parles de coordonnées comme un exemple de donnée scalaire non atomique, je vois bien un moyen de les rendre scalaires, au moins pour les nombres entiers: utiliser un entier long pour coder l'abscisse sur les octets de poids fort et l'ordonnée sur les octets de poids faible. Au final le type entier long est un scalaire atomique. Avec des flottants on pourrait jouer sur les octets aussi mais plus difficilement, c'est vrai.

ça marche pas !

Heureusement d'ailleurs, parce qu'au sens strict, on pourrait considérer que le type "chaîne de caractères" n'est pas vraiment un scalaire atomique dès lors qu'il a une longueur variable. Pourtant il n'y a aucun problème à indexer des chaînes car on a déjà un ordre qui était utilisé bien avant l'invention de l'informatique: l'ordre alphabétique!!!

Une chaine de caractères est sémantiquement atomique (si bien normalisée). Les SGBDR procèdent par manipulation de données sémantique (E. F. Codd) et non par manipulation d'octets...

A +

[SQLpro]

Si un point B(xB,yB) est à moins de 5 kilomètres de A(xA,yA) alors |xB - xA| < 5 km et |yB - yA| < 5 km, car le carré de 10 km autour de A englobe entièrement le cercle de 5 km autour de A. Donc si c'est pour trouver les points à une distance donnée, j'aurais plutôt tendance à créer deux indexes, 1 pour l'abscisse et un pour l'ordonnée, à filtrer sur l'intervalle (-5, +5) et ensuite regarder parmi les points restants ceux qui sont effectivement dans le cercle.

NON, ça ne marche pas !

...Avec PostgreSQL je peux déjà indexer un champ dans XML ou dans JSON, sur les autres serveurs je pensais que c'était déjà le cas mais je suppose que ça viendra.

Ce que tu affirmes est complément faux !
PostGreSQL ne sait pas indexer une colonne XML !

A +

[SQLpro]

...Les grilles, c'est pas franchement la rolls de l'indexation spatiale (définition d'un pas, connaissance bbox globale à priori, taille de l'indexe,...). PostGIS s'appuie sur des RTree pour indexer les boites englobantes (voir Spatial Indexing) (il n'y a pas ce genre de méthode d'indexation dans SQL Server?)

Les "grilles" restent ce qu'il y a de plus efficace en matière d'indexation spatiale. SQL Server utilise deux méthodes de recherches :

• les QuadTree (proche de la notion de RTree) qui permettent un accès
• les courbes de Hilbert (en fait une courbe de Lebesgues)

...

Pour revenir au sujet de départ, il y a toujours quelqu'un qui paie directement ou indirectement pour un logiciel de base de données open source. Il est intéressant de s'interroger sur les motivations du côté des utilisateurs et des fournisseurs. Si on prend le cas de PostGIS qui justifie selon moi l'utilisation de PostgreSQL pour pas mal d'acteur du monde du SIG :

- Quand le gros de la valeur ajoutée est dans son produit fini, il n'est pas déraisonnable de payer l'ajout d'une fonctionnalité à PostGIS pour ne payer une licence sur un SGBR non libre ou être le seul à maintenir un algorithme de calcul géométrique.

- Quand on développe un logiciel type SIG, inclure une dépendance à un SGBD payant fait monter la facture des clients et rend non compétitif face à ceux qui mettent en commun leurs efforts autour de PostGIS et se concurrencent sur le développement des surcouches et interfaces.

- Quand on est un contribuable, il peut être rentable de financer indirectement PostGIS via des projets de recherche s'appuyant sur ce dernier, plutôt que de n'avoir aucune alternative au paiement d'une licence de SGBD par processeur dans chaque organisation exploitant des données spatiales.

- Quand on est développeur, il peut être aussi intéressant de se rémunérer en donnant des formations sur PostGIS et en développant des fonctionnalités à façon ou en surcouche, qu'en étant salarié d'une boite fournissant un SGBD payant.

Après, des bugs, il y en a sûrement... La différence entre l'opensource gratuit et le payant fermé en la matière, c'est que lorsqu'un bug pose vraiment des problèmes, on a d'autres options que se battre à faire admettre au fournisseur que le bug existe et qu'il faut le traiter : Payer un indépendant ou mettre les mains dans le cambouis.

Pour une fois, je te rejoins à 100 % sur ces propos....

PG / PostGIS est excellent pour les collectivités locales... Comme il s'agit de fonctionnaire on peut faire la maintenance des bases la nuit ou le weekend, car PG est bloquant avec son VACUUM de m...

En revanche si c'est pour la cartographie du contrôle aérien, alors là 24/24 et 7/7 pas de PG !

A +

[SQLpro]

...

Je travaille actuellement dans le monde de la traduction et on a un problème similaire pour trouver des phrases similaires (le but étant de s'inspirer d'anciennes traductions pour assurer la cohérence dans le temps). La fonction qui calcule la distance entre deux phrases est en O(N²) et aucun index ne changera cela. En revanche, en utilisant certains découpages de chaînes on peut créer un index qui élimine une grosse partie des phrases qui n'ont aucune chance de rester proches de la phrase en cours. Mais un deuxième filtrage reste nécessaire ensuite.

T'es tu intéressé à la méthode, bien plus rapide de calcul de distance entre deux motif que j'ai inventé il y a une vingtaine d'années ? (inférence basique)
https://sqlpro.developpez.com/cours/...ns-motifs/#LVI
Elle est très utilisée notamment par la préfecture de police... (avec du PG d'ailleurs).

A +

[bretus]

PG / PostGIS est excellent pour les collectivités locales... Comme il s'agit de fonctionnaire on peut faire la maintenance des bases la nuit ou le weekend, car PG est bloquant avec son VACUUM de m...

J'imagine que les gens qui gèrent les PostgreSQL de bricoles comme OpenStreetMap arrivent à contourner ce genre de problème non?

Ce qui est sûrement vrai, c'est que tu as vraisemblablement moins d'exploitation et de bidouilles à faire avec un SQL Server ou autre.

[SQLpro]

Le volume n'a rien à voir avec la "dynamicité" des données. Dans la mesure ou ces données sont quasi statiques (les routes ne changent pas de "dessin" toutes les 2 secondes, contrairement aux avions en vol...) il n'y a donc que très peu de mises à jour, ce qui ne pose pas de problème...
En revanche, pour des bases de données ou il y a beaucoup de transactions, PG n'est franchement pas adapté !

A +

[esperanto]

Dans ce type d'organisation, un index a clef composite contenant par exemple les colonnes A, B, C (dans ce sens... en fait ce sont des vecteurs...)

Code :

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

CREATE INDEX X ON T (A, B, C)

Sera tres efficace pour les recherches suivantes :

On est d'accord. Pour faire court, l'index est utilisé si et seulement si au moins la première colonne est utilisée, et ensuite si la deuxième, la troisième... apparaît dans la requête.
Il faut donc choisir judicieusement l'ordre des colonnes, en fonction des requêtes qu'on risque de faire le plus souvent. Parfois c'est assez facile: par exemple pour une adresse, on indexera d'abord sur le nom de la ville ou le code postal plutôt que sur la rue, parce que la recherche sur la seule ville est plus fréquente que la recherche d'une rue. Pour des coordonnées spatiales, le choix est moins évident, mais d'un autre côté, quelle proportion des requêtes porteront sur une seule coordonnée? Il vaut donc mieux connaître à l'avance les requêtes qui seront faites et faire au cas par cas.

Trouve moi la relation d'ordre valide d'un point géographique ayant deux coordonnées X et Y ?
Si tu défini l'ordre sur X, cela ne permet pas la recherche en X/Y, et inversement.

Quand tu crées un index sur 2 colonnes, ça revient en réalité à utiliser la relation d'ordre x1 < x2 ou (x1 = x2 et y1 <= y2), qui est bien une relation d'ordre total.
Après, il est vrai que cette relation d'ordre ne permet pas d'avoir un index répondant à la question des distances, sauf que ce n'est pas l'existence de deux colonnes mais la complexité de la fonction distance (en particulier son inégalité triangulaire qui fait qu'elles ne s'ajoutent pas) qui pose problème.

ça peut arriver aussi avec une donnée scalaire. Je vais prendre ici un exemple volontairement caricatural mais que j'ai quand même pu tester à l'instant avec PostgreSQL.
Soit donc un champ x de type float avec un index. Si je fais une requête "where x < 1000" la commande EXPLAIN me confirme que l'index est utilisé. Si j'écris par contre "where log(x) < 3" il n'est pas utilisé, EXPLAIN évoque un scan. J'obtiens le même résultat (si log correspond au logarithme décimal) mais sans index donc en plus de temps. On peut certes corriger en créant un index sur log(x) mais tu l'as dit toi-même ça ne marche que si la fonction a des propriétés très particulières, donc ce n'est pas toujours possible.

Hier j'ai expliqué ici que pour répondre à une requête avec des distances, j'aurais tendance à faire en deux étapes: utiliser l'index pour filtrer les données qui sont franchement en dehors du cercle, mais ensuite calculer les distances uniquement sur les valeurs retournées par l'index. J'avais alors parlé de faire deux requêtes, mais j'ai continué d'y réfléchir et je me suis rendu compte qu'on peut quand même le faire en une seule requête. Et là encore j'ai testé avec PostgreSQL avant de valider.
CREATE TABLE test (x float, y float); create index on test(x,y);
Je rajoute 1 million de lignes de façon aléatoire.
Maintenant je définis distance(x,y) -> sqrt(x² + y²): pour simplifier, je me limite à la distance par rapport à l'origine.
Et maintenant, testons:

1. la requête "select count(*) from test where distance(x,y) < 0.1" n'utilise pas l'index et répond en 430 ms.
2. la requête "select count(*) from test where x > -0.1 and x < 0.1 and y > 0.1 and y < 0.1" utilise l'index et répond en 79 ms
3. et là ça devient intéressant, la requête "select count(*) from test where x > -0.1 and x < 0.1 and y > 0.1 and y < 0.1 and distance(x,y) < 0.1" utilise l'index et répond en 109 ms!!!

Ici l'index a rempli le rôle que je lui avais assigné: il n'a pas donné la liste des points dans le cercle de rayon 0.1, mais il a filtré suffisamment de points extérieurs au cercle pour que le second filtrage avec la fonction distance prenne relativement peu de temps! Ah oui et évidemment j'ai bien vérifié que les requêtes 1 et 3 donnent le même résultat!

Donc oui, quand on a plusieurs colonnes il faut réfléchir un peu plus, mais les index sont quand même utilisables. Et encore, il semble qu'il existe d'autres types d'index plus spécifiques à un tel usage, comme ceux que tu cites dans ton lien, le fait que ça marche mieux ne signifie pas que d'autres techniques ne marchent pas du tout. Au passage je rappelle que j'avais écrit tout ça suite à une remarque concernant les bases de données objet, or à aucun moment il n'a été question d'objets dans le présent message.

Un petit exemple est donnée par PostGreSQL qui ne sait toujours pas indexer du XML, mais propose d'indexer certaines portions du XML par le biais de fonctions... Il faudrait donc que je créé autant d'index sur fonction qu'il y a de balises et d'attributs XML...

Rassure-moi là, pour en rester à du SQL, quand tu as une table avec plusieurs colonnes, tu ne vas quand même pas systématiquement toutes les indexer, si? Dans du XML, il est quand même fréquent qu'il y ait beaucoup de données peu utiles ou peu propices à une recherche, alors on indexe uniquement les attributs ou les nœuds qui sont susceptibles d'apparaître dans une clause WHERE. C'est valable aussi en SQL d'ailleurs. Dans mon projet avec les traductions, je n'ai par exemple jamais indexé la colonne qui contient l'auteur: cette donnée est très utile à afficher, mais comme l'outil de traduction n'a pas ce champ dans ses critères de recherche, aucune raison de l'indexer.

[*]cela ne règle pas le problème de document arrivant avec de nouvelles balises et/ou attributs

En effet, on touche là un point que j'ai toujours regretté sur les serveurs de données XML, même ceux qui étaient dédiés comme Tamino: ils ne permettaient pas (à moins que ça soit apparu dans des versions récentes) de typer le XML par un schéma ou une DTD, ce qui aurait pu assurer la cohérence. De même dans un XMLTYPE on obtiendrait de meilleures performances si on pouvait affecter un schéma à chaque colonne, et tu choisirais ensuite les champs à indexer en lisant le schéma et en te demandant quelles requêtes tu souhaites faire. Avec du XML ou du JSON sans schéma c'est évidemment plus compliqué. Avec des bases orientées objet, on se retrouvait finalement dans une situation similaire à du XML avec schéma.

Pourtant SQL Server propose une indexation générique des colonnes XML... Et c'est plutôt bluffant à l'usage !

Je veux bien te croire mais aurais-tu des exemples de requêtes que tu fais avec ces index là? Parce que pour le moment, indexer tout le XML plutôt que certains champs individuellement, j'ai un peu de mal à voir le cas d'usage.

NON, ça ne marche pas !

J'avais déjà répondu plus tôt qu'en effet, après avoir testé, j'ai pu voir que seul un des indexes était utilisé, donc ok sur ce point-là je me suis trompé. Mais un seul index, c'est toujours mieux qu'aucun, donc on ne peut pas dire que ça ne marche pas du tout.

PostGreSQL ne sait pas indexer une colonne XML !

Encore une fois tout dépend de ce que tu entends par indexer une colonne XML: comme dit plus haut, je ne vois pas encore le cas d'usage pour une indexation totale, mais pour des champs en particulier, oui. Après je ne dis pas que c'est totalement inutile, je dis juste que pour ce point-là je ne sais pas.

T'es tu intéressé à la méthode, bien plus rapide de calcul de distance entre deux motif que j'ai inventé il y a une vingtaine d'années ? (inférence basique)
https://sqlpro.developpez.com/cours/...ns-motifs/#LVI
Elle est très utilisée notamment par la préfecture de police... (avec du PG d'ailleurs).

Là pour le coup ça m'intéresse bien, comme ton lien cite la distance de Levenshtein je vois bien que ça pourrait correspondre à mon cas d'usage. OK je vais tester ça, je ne ferai pas de commentaires pour le moment.
En tout cas non je n'avais jamais vu ce lien, donc la conversation est utile!

En revanche, pour des bases de données ou il y a beaucoup de transactions, PG n'est franchement pas adapté !

C'est peut-être vrai, mais à l'inverse, quand il y a peu de transactions, Oracle ou SQL server sont-ils vraiment adaptés? Comme tu peux le voir ici, je ne défends pas l'idée du tout PostgreSQL, plutôt celle du cas par cas. Mais c'est vrai que je n'ai cité que des bases de données libres, je n'ai jamais acheté une base propriétaire pour mon propre usage, le cas où il y a "beaucoup de transactions", comme tu dis, n'étant simplement pas le mien.

[walfrat]

Le volume n'a rien à voir avec la "dynamicité" des données. Dans la mesure ou ces données sont quasi statiques (les routes ne changent pas de "dessin" toutes les 2 secondes, contrairement aux avions en vol...) il n'y a donc que très peu de mises à jour, ce qui ne pose pas de problème...
En revanche, pour des bases de données ou il y a beaucoup de transactions, PG n'est franchement pas adapté !

A +

Je ne vais pas douter de ce que tu dis, mais le problème est de définir "beaucoup". En outre si on prend ton exemple des avions en vols cela ne m'a pas l'air d'être le meilleur exemple que l'on puisse avoir, as t'on vraiment besoin de tout mettre un rafraîchissement des positions des avions toutes les 2s sur le dos d'un SGBD ? Ca me parait plutôt être un cas pour du streaming de données, potentiellement un mix peut-être (dernière position stocké une fois par minute, streaming de dernières données toutes les 2s) ? Ce n'est pas parce que certaine base le gère mieux qu'utiliser un SGBD reste une solution adapté au problème.

[SQLpro]

Hier j'ai expliqué ici que pour répondre à une requête avec des distances, j'aurais tendance à faire en deux étapes: utiliser l'index pour filtrer les données qui sont franchement en dehors du cercle, mais ensuite calculer les distances uniquement sur les valeurs retournées par l'index. J'avais alors parlé de faire deux requêtes, mais j'ai continué d'y réfléchir et je me suis rendu compte qu'on peut quand même le faire en une seule requête. Et là encore j'ai testé avec PostgreSQL avant de valider.
CREATE TABLE test (x float, y float); create index on test(x,y);
Je rajoute 1 million de lignes de façon aléatoire.
Maintenant je définis distance(x,y) -> sqrt(x² + y²): pour simplifier, je me limite à la distance par rapport à l'origine.
Et maintenant, testons:

1. la requête "select count(*) from test where distance(x,y) < 0.1" n'utilise pas l'index et répond en 430 ms.
2. la requête "select count(*) from test where x > -0.1 and x < 0.1 and y > 0.1 and y < 0.1" utilise l'index et répond en 79 ms
3. et là ça devient intéressant, la requête "select count(*) from test where x > -0.1 and x < 0.1 and y > 0.1 and y < 0.1 and distance(x,y) < 0.1" utilise l'index et répond en 109 ms!!!

Ici l'index a rempli le rôle que je lui avais assigné: il n'a pas donné la liste des points dans le cercle de rayon 0.1, mais il a filtré suffisamment de points extérieurs au cercle pour que le second filtrage avec la fonction distance prenne relativement peu de temps! Ah oui et évidemment j'ai bien vérifié que les requêtes 1 et 3 donnent le même résultat!

Encore une fois vous êtes dans l'erreur ! En effet le terme "utiliser un index" ne veut pas dire qu'il tire profit de la puissance de l'index en recherche.
En présence d'une table le seul accès possible aux données consiste à faire une balayage de la table. C'est lent, c'est moche c'est stupide.
On a donc inventé les index pour accéder le plus rapidement aux données. Les index ce sont un rangement des données particulier associé à un algorithme de recherche.

Les différences qui existent entre un index et une table sont donc suivantes :

• Dans un index les données sont rangées (par exemple dans un ordre de tri). Dans une table elle sont dans un ordre quelconque totalement indifférent pour ne pas dire aléatoire.
• Dans un index, l'accès aux données se fait avec un mécanisme de dichotomie (je devrais dire de n-chotomie) et généralement c'est un arbre de recherche (Btree, BWTree, BTree+, QudTree...). Dans une table l'accès se fait par une liste chainée ce qui oblige à lire séquentiellement les données.

L'accès à un index peut donc se faire de deux manières :

• En mode recherche (on appelle cela un "seek") et dans ce cas il y a utilisation de l'arbre de recherche. Ceci suppose que le prédicat de recherche est "sargable" (cherchable).
• En mode séquentiel (on apelle cela un "scan" - balayage) et dans ce cas, l'accès est exactement le même que dans une table non indexée… C'est le cas lorsque le prédicat de n'est pas cherchable.

Comme vous n'avez pas analysé correctement l'usage de l'index, dans le plan d'exécution, vous n'avez pas remarqué qu'il n'utilisait pas un seek, mais un scan…
En effet, il est souvent légèrement plus rapide d'accéder en mode scan à un index plutôt qu'à une table, tout simplement par ce que, si l'index contient toutes les données de la requête, son "poids" en terme de volumétrie de données est souvent moindre !
Pour votre information, PostgreSQL s'est mit très tardivement à faire des scan d'index, alors que tous les autres SGBDR en faisaient depuis des décennies…

Tout ce que je viens de dire est expliqué en long en large et en travers dans mon livre sur le langage SQL ou je consacre un chapitre au sujet des index, leurs différents types, utilisation et efficacité…
Alors, commencez par vous former, lire les livres…

Rassure-moi là, pour en rester à du SQL, quand tu as une table avec plusieurs colonnes, tu ne vas quand même pas systématiquement toutes les indexer, si?

Encore une fois, votre méconnaissance lié à votre enfermement dans le monde PostgreSQL, ne vous permet pas de déciller vos yeux ! Il existe des techniques d'indexation, permettant d'indexer TOUTES les colonnes des tables, et elles sont d'une redoutable efficacité. En particulier dans Microsoft SQL Server vous pouvez indexer l'intégralité des colonnes de la table (à condition qu'elle ne possède pas plus de 1024 colonnes) avec le concept de clustered columnstore index.
Exemple :

Code :

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

CREATE CLUSTERED COLUMNSTORE INDEX XC ON MaTable;

Il est inutile de préciser les colonnes à indexer puisque dans ce cas elles sont TOUTES indexées… Ce type d'index, dit "vertical" est arrivé avec la version 2014 de SQL Server, soit il y a 6 ans…
Evidemment c'est de la R&D de haute volée qui permet cela et c'est actuellement hors de portée des bidouilleurs de PG... Il y a bien eu quelques tentatives d'essayer de faire de l'indexation verticale dans PG... Mais tous les projets ont avortés…
À lire : https://wiki.postgresql.org/wiki/Fut...lumnar_indexes
Sauf dans le monde payant : Fujitsu propose une telle extension dans sa version payante de « Fujitsu Enterprise Postgres ».

Bref on en revient au point initial du post… ne pas payer et avoir une daube.. ou payer encore plus cher !

Dans du XML, il est quand même fréquent qu'il y ait beaucoup de données peu utiles ou peu propices à une recherche, alors on indexe uniquement les attributs ou les nœuds qui sont susceptibles d'apparaître dans une clause WHERE. C'est valable aussi en SQL d'ailleurs. Dans mon projet avec les traductions, je n'ai par exemple jamais indexé la colonne qui contient l'auteur: cette donnée est très utile à afficher, mais comme l'outil de traduction n'a pas ce champ dans ses critères de recherche, aucune raison de l'indexer.

De la même façon que tu ne peut à l'avance savoir comment vont être utilisées les données relationnelles d'une base et donc sans arrêt réagir à créer des index en fonction des requêtes pratiquées, tu ne peut savoir à l'avance comment va être utiliser le XML stocké. C'est pourquoi un système d'indexation générique du XML comme celui de Microsoft SQL Server est très efficace et n'oblige pas les DBA à intervenir tous les 4 matins, pour rajouter une pelletée d'index….

Pour votre information encore, SQL Azure pratique depuis quelques temps, l'indexation automatique en fonction de la charge et des requêtes que lancent les utilisateurs. Ceci ne devrait pas tarder non plus à arriver dans la version "on premise" de SQL Server… En tout cas, les métadonnées des tables systèmes en ont déjà la trace...

En effet, on touche là un point que j'ai toujours regretté sur les serveurs de données XML, même ceux qui étaient dédiés comme Tamino: ils ne permettaient pas (à moins que ça soit apparu dans des versions récentes) de typer le XML par un schéma ou une DTD, ce qui aurait pu assurer la cohérence.

SQL Server fait cela depuis la version 2005.... Mais la R&D de MS c'est à peu près 10000 x plus que Tamino ! C'est là toute la différence….

Je veux bien te croire mais aurais-tu des exemples de requêtes que tu fais avec ces index là?
Parce que pour le moment, indexer tout le XML plutôt que certains champs individuellement, j'ai un peu de mal à voir le cas d'usage.

Bien sûr que oui… Pour info j'ai été conseil de Intellixir en 2008 (racheté depuis par Questel) qui faisait du text mining avec MS SQL Server 2005 en intégrant des brevets sous forme XML pour analyse. Quand je suis arrivé il faisait une moulinette en code pour intégrer les données XML parsées en .net dans la base. Cela mettait 20 minutes pour digérer 500 documents.
En les intégrant directement par BULK LOAD dans une table de stagging, puis en ajoutant l'index XML, et finalement en parsant les données avec des requêtes XPath/XQuery nous avons finit par intégrer les mêmes documents en moins de 15 secondes… chargement, indexation et intégration dans les tables relationnelles comprise !!!

Pour information, j'ai récemment été demandé pour ce même genre de projet dans le cadre des universités française (intégration des thèses des doctorants de l'ensemble des établissement français)… Quelques To de doc XML !!!!!

Evidemment quand on reste scotché à PG qui est plus que nullissime en matière de XML, on à des œillères ! Voici ce que je dit dans mon article à paraître sur le portage SQL Server vers PG (extrait) :
"
L'implémentation du type XML diffère de façon très notable entre SQL Server et PostGreSQL.
SQL Server ne suit pas la norme SQL, jugée trop restrictive, et utilise ses propres méthodes avec une implémentation quasi complète de XQuery et XPath.
PostGreSQL suit la norme, mais son implémentation de XPath pour manipuler les données en est resté à la version 1.0 (datant de 2003), et n’utilise pas XQuery.
…
La pauvreté de ces fonctions en terme d'opérabilité du fait de la limitation à XPath version 1.0 et en l’absence de XQuery, ne correspond que peu ou pas à ce que faire SQL Server et il faudra soit développer des fonctions ou des procédures pour s'en approcher, soit décider d'abandonner tout le code manipulant du XML pour le déplacer vers l'application.
En particulier PostGreSQL ne permet pas de modifier des informations situées à l’intérieur d’un document XML. Il faudra externaliser de la colonne et la mettant dans une variable, modifier le contenu de cette variable puis le réinsérer dans la colonne par un UPDATE, le tout dans une transaction explicite associé à un verrouillage exclusif de cette ligne de table via la commande pseudo-SQL SELECT FOR UPDATE.
…
Pour produire du XML à partir de données, PostGreSQL utilise une technique ligne à ligne à partir d'un curseur... La requête n'est donc pas optimisée.
Les fonctions pour ce faire sont les suivantes :
• table_to_xml
• query_to_xml
• cursor_to_xml
Cette technique empêche de produire des hiérarchies XML dans le sens ou toutes les données sont « à plat ».
"


A +

[walfrat]

Mais c'est quoi le but au juste ? De montrer que les solution payantes sont plus performantes ?

Ok, mais combien de projets ont besoins de ces meilleurs performances ?

D'ailleurs je viens de me rappeler quelque chose, cependant je ne saurais dire combien ça pèse dans l'équation.

J'ai connu par exemple des solutions de Gestion Electronique de Document qui couplent des solution compatible SGBDR gratuit + Lucène ou Elastic Search (Alfresco), pour des recherches plus souples suffisant pour des petits/moyens projets, le tout restant gratuit. Ce qui permet au produit de facturer plein pot puisque permettant d'utiliser des composants gratuits.

Ce que je vois dans mon monde c'est que les personnes fuient les solutions payantes. Il s'agit basiquement de projets qui à l'époque sortaient un Oracle pour quelques centaines de tables et quelques centaines de milliers d'enregistrement.

[Sodium]

Moi j'attends toujours des sources autres que les écrits de SQL Pro démontrant la supériorité de SQL Server par rapport à un autre SGDB quel qu'il soit

[StringBuilder]

Mais c'est quoi le but au juste ? De montrer que les solution payantes sont plus performantes ?

[...]

Ce que je vois dans mon monde c'est que les personnes fuient les solutions payantes. Il s'agit basiquement de projets qui à l'époque sortaient un Oracle pour quelques centaines de tables et quelques centaines de milliers d'enregistrement.

Le sujet ici, n'est pas trop de savoir pourquoi les gens choisissent des solutions payantes ou gratuites, ni de savoir si les solutions payantes sont meilleures que les solutions gratuite... mais de se demander pourquoi personne de paie pour un logiciel de base de données open source.

Petit rappel : propriétaire ne veut pas dire payant, et open source ne veut pas dire gratuit.

Donc à partir de là, votre exemple et vos retours d'information tombent un peu à l'eau.

Ensuite, le constat est le suivant :
- Comme vous, bon nombre de personnes confondent "open source" et "gratuit". Par conséquent, de prime abord, quand une entreprise choisi une solution "open source" c'est en grande partie avec comme arrière pensée qu'elle n'aura pas à payer pour cette solution. Par conséquent, elle ne va pas spontanément faire une donation, ni souscrire à une quelconque offre payante. C'est, je pense, la principale raison pour laquelle peu de personnes paient pour des solutions open source.
- Une large majorité de personnes qui choisissent l'open source (en confondant avec gratuité) le font par manque de moyens. En effet, tout le monde n'a pas plusieurs milliers (dizaines) à mettre dans des licences, surtout pour des applications non critiques. A nouveau, ces personnes sont moins sujettes à gratifier le travail de ceux qui leur fournisseur une solution gratuite.

Pour ce qui est du débat entre SQL Server et le reste du monde (ou alors les solutions "propriétaires" et les solutions "open source") il y a pas mal de choses à garder en tête...
- L'open source a l'avantage que le premier bidouilleur venu (enfin, c'est complètement faux mais on va garder l'argument quand même) peut contribuer à l'amélioration du produit. Ceci permet, en théorie, d'avoir des milliers de contributeurs, ce qui est bien plus important que les moyens que peut se permettre une entreprise privée.
- Parfois les éditeurs privés dédaignent des fonctionnalités sur leur produits (par exemple, Microsoft à une époque avec la sécurité) et un réel besoin de solution alternative émergent, et de nombreuses sociétés sont prêtes à investir massivement (comme Novell avec Mono pour porter .NET sous Linux)

C'est ce qu'il s'est passé il y a une vingtaine (trentaine ?) d'années avec les bases de données : les bases de données étaient destinées aux gros systèmes, extrêmement onéreuses, et pas du tout adaptées aux petits projets ne disposant que de faibles moyens. Sont alors apparus, entre autres, MySQL et PostGreSQL : des bases de données de plus faible dimension, peu onéreuses à mettre en place.

Cependant, aujourd'hui, Microsoft, tout comme les autres acteurs des SGBD, cible aussi les PME, et même pire, proposent des versions gratuites parfaitement utilisables.
De même, à ce jour, à ma connaissance, Microsoft tout du moins, ne délaisse pas grand chose sur SQL Server : niveau sécurité, c'est un des meilleurs (si ce n'est le meilleur), niveau performances aussi, et niveau coût... aussi. Oracle est en train de payer très cher d'avoir voulu se reposer sur ses laurier par contre.

Ainsi, pour une PME, ou grosse boîte qui a un projet non critique, il est parfaitement possible d'utiliser SQL Server Express, par exemple, dont les limites sont suffisamment larges pour pouvoir soutenir la comparaison avec des offres gratuites de MySQL ou PostgreSQL dans la plupart des cas.
L'avantage de rendre du SQL Server, c'est qu'on bénéficie du support de Microsoft, y compris avec la version Express. Ainsi, on a tous les correctifs "au plus tôt" qu'ils soient de sécurité ou qualité. On a aussi la garantie d'avoir tout les outils pour changer de version de façon quasi transparente, etc.

[walfrat]

Effectivement j'ai écris cela très vite, et fait un gros abus de language, mes excuses.

Je vais donc corriger mais d'abord :

Le sujet ici, n'est pas trop de savoir pourquoi les gens choisissent des solutions payantes ou gratuites, ni de savoir si les solutions payantes sont meilleures que les solutions gratuite... mais de se demander pourquoi personne de paie pour un logiciel de base de données open source.

Il s'avère que l'info donné par l'article compare le nombre brut de solution open source gratuite vs la partie payante, on peut donc déjà critiquer l'absence d'information sur proportion de solution propriétaire payante par rapport à la version gratuite quand elle existe. En outre on pourrait aussi demander une répartition des projets selon leur importance (volume de données ? budget ?) en la matière.

Et voici donc mon argument plus complet :

• Des produits promettent d'être compatible avec des solutions SGBDR open-source ou propriétaire mais sans nécessairement avoir besoin de ce que le côté payant apporte pour beaucoup de projets qui ne sont pas suffisamment gros. Ce qui leur permet de vendre plus facilement leurs licences.
• Ce que je vois dans mon monde c'est que les personnes fuient les solutions à l'ancienne, basé sur du propriétaire payant (AGL + Oracle qui tourne depuis 25ans) et se tourne vers de l'open-source version gratuit.

Concernant mon deuxième argument, je ne dis pas qu'ils ont forcément raison. J'imagine que certains ne sont donc pas nécessairement au courant de SQL Server version gratuite ou s'en font une mauvaise image. En soi ça n'empêche pas beaucoup de projet d'obtenir un résultat médiocre, même par rapport au performance possible du produit open-source version gratuite. J'ai vu un certain nombre de projets déjà de plutôt moyenne envergure (100aines de milliers a 5 millions de données dans les tables) et il s'agit clairement plus d'un problème du modèle de données, ou de sa mauvaise exploitation que de la solution technique sur laquelle elle repose.

- L'open source a l'avantage que le premier bidouilleur venu (enfin, c'est complètement faux mais on va garder l'argument quand même) peut contribuer à l'amélioration du produit. Ceci permet, en théorie, d'avoir des milliers de contributeurs, ce qui est bien plus important que les moyens que peut se permettre une entreprise privée.
- Parfois les éditeurs privés dédaignent des fonctionnalités sur leur produits (par exemple, Microsoft à une époque avec la sécurité) et un réel besoin de solution alternative émergent, et de nombreuses sociétés sont prêtes à investir massivement (comme Novell avec Mono pour porter .NET sous Linux)

Je suis d'accord. Quand il s'agit de travaux poussés sur basé sur de la R&D de pointe, ce n'est pas à la portée du premier bidouilleur venu. Parfois nous avons des éléments open-source très poussés, parfois non.

[StringBuilder]

Je suis d'accord. Quand il s'agit de travaux poussés sur basé sur de la R&D de pointe, ce n'est pas à la portée du premier bidouilleur venu. Parfois nous avons des éléments open-source très poussés, parfois non.

Outre la complexité, le côté "c'est open source donc n'importe qui peut contribuer" est faux.

En effet, c'est open source, donc n'importe qui peut récupérer le source, le modifier, et utiliser une version modifiée par ses soins.

Mais ce n'est pas pour autant qu'on devient contributeur de la solution : le projet est généralement géré par une équipe plus ou moins réduire, qui ne voit pas forcément d'un bon œil les patchs de chacun.

Donc si demain une personne décide de mettre en place une super solution qui permet de faire de la haute dispo sous PostgreSQL, mais qui casse en contre-partie certaines autres chose, alors ce n'est pas pour autant que PostgreSQL saura faire de la haute dispo du jour au lendemain.

J'ai longtemps développé de nombreux patches pour un jeu open source Open Transport Tycoon Deluxe mais même si au final, après quelques années, la plupart de mes patches ont été réécrits et intégrés dans le trunk (probablement de manière plus élégante, je veux bien l'admettre) pas une seule ligne de mes patches originaux n'ont été inclus dans le trunk.
C'est très frustrant, mais aussi très dommageable : il en résulte de nombreux forks (par exemple le JGRennison Patch Pack, mais ce n'est pas sans risque de les utiliser (ici, c'est un jeu, mais les problèmes sont les mêmes avec un SGBD) :
- On devient alors dépendant de la bonne volontée d'une tierce personne (ou équipe, entreprise) qui peut du jour au lendemain décider de rendre son fork payant, d'arrêter de le faire évoluer, etc.
- A chaque évolution du trunk, ce sont des milliers de parties du code qui ne compilent plus (ici,on parle de quelques centaines de milliers de ligne de code... je vous laisse imaginer ce que ça peut donner avec un SGBD
- En cas de refonte majeure d'une fonctionnalité dans le trunk... bah pas le choix, on ne peut plus simplement merger et debugger, il faut tout réécrire : perte de temps phénoménale et risques majeurs de régressions graves
- Une fois qu'on a commencé à intégrer des spécificités du fork, notre projet n'est plus compatible avec le trunk : en cas de divorce forcé avec le fork, on perd tout, ou presque

C'est pour ces raisons (qui résultent d'une expérience personnelle réelle) que j'estime l'open source très largement sur-vendu : oui, tu peux bidouiller le code pour ton propre usage. Non, tu ne peux pas bénéficier des milliers d'évolutions apportées par les milliers d'autres utilisateurs... et quand on arrive à récupérer quelques évolutions des autres, ce n'est pas sans risque !

[Luc Orient]

... Dans une table elle sont dans un ordre quelconque totalement indifférent pour ne pas dire aléatoire.

Et la notion d'Index " plaçant " ( CLUSTER en anglais ) alors ... Comme dans Db2 for z/OS par exemple ?

[walfrat]

StringBuilder,

Un développement incluant plusieurs personnes va forcément devoir avoir des personnes plus investis qui vont être plus investis et généralement à la base du projet, autrement dit une équipe "core" et des contributeurs autours. En outre peu importe la façon dont c'est organisé, il y aura toujours un moment où quelqu'un ne sera pas d'accord.

Ainsi un argument qu'on peut apporter en se "mettant du côté" des pro open source c'est que si tu n'es pas content tu peux forks. C'est vrai, l'argument marche, après il y a des limites au fork car rien n'est parfait. Si tu commences à fork et veut récupérer des travaux des autres fork ou du main, ça risque effectivement de finir par t'exploser à la figure.

On peut donc raisonnablement dire que c'est toujours "techniquement" possible. Mais dans la réalité tu vas probablement finir par te heurter à tes limites en compétences (et le fais qu'on ne peut pas connaître tout en info) mais surtout à la limite d'effort que tu es prêt à t'investir pour ce que tu veux faire. Et la on peut dire qu'on se retrouve au finale proche du comportement d'une entreprise. Une entreprise va limiter l'effort qu'elle est prêt à faire via le budget qu'elle va allouer. Un contributeur de l'open source lui va limiter l'effort qu'il est prêt à faire par rapport au temps qu'il veut allouer (je grossis un peu le trait).

[Sodium]

J'ai eu le cas il y a un an ou deux d'un logiciel (très cher) avec lequel on développait une application mobile et celle-ci était inutilisable parce que le wrapper ne gérait pas correctement le clavier virtuel. Après avoir contacté le support technique (payant), ils ont fini par corriger le truc au bout de quelques mois. Inutile de dire que si on avait eu un impératif de date de mise en prod, on aurait été sacrément dans la merde.

L'open source c'est aussi ça, une transparence au niveau du processus de développement. On peut voir quels problèmes ou demandes d'évolution ont été remontés, les réponses apportées, d'éventuels fix temporaires en attendant que ça ait été définitivement intégré au code source.

[Invité]

C'est pour ces raisons (qui résultent d'une expérience personnelle réelle) que j'estime l'open source très largement sur-vendu

Comme tu le dis, il s'agit d'UNE expérience personnelle réelle. Et en plus dans le domaine du jeu vidéo, qui n'est pas réputé pour être le plus "humainement bienveillant".

Désolé mais tu ne peux pas généraliser cette expérience à tout l'open-source. Personnellement, j'ai contribué à au moins 10 projets open-source, dans des domaines variés; mes patches ont généralement été intégrés et, même quand ce n'était pas le cas, j'ai toujours été bien accueilli.

[frenchlover2]

Le seule moyen de financer l'Open source est de mettre des publicité pour les version gratuite et plus de fonctionnalité et sans pub pour les version complète , les gens sont radin ils ne payent pas