CREATE INDEX & WHERE

Bonjour à tous,

J'ai lu que pour une recherche plus efficace dans une table, on pouvait faire plusieurs index avec des WHERE.

Par exemple, pour une table "books" avec les colonnes :
- id
- id_category
- title
- description

Au lieu de faire :

Code:

---

1 2	CREATE INDEX idx_books_description ON books USING gist(title gist_trgm_ops)

---

On pouvait faire :

Code:

---

1 2 3 4 5

CREATE INDEX idx_books_description ON books USING gist(title gist_trgm_ops) WHERE id_category = 1; CREATE INDEX idx_books_description ON books USING gist(title gist_trgm_ops) WHERE id_category = 2; CREATE INDEX idx_books_description ON books USING gist(title gist_trgm_ops) WHERE id_category = 3; etc.

---

Et après lorsque l'on va rechercher un titre, on place un "WHERE category = x" avant le code de recherche. Cela permet d'utiliser un index moins gros et donc d'améliorer les performances.

Je possède ces tables :

régions
- id
- name

rues
- id
- name

region_rues
- id
- id_region
- id_rue

Et j'aimerais tester l'indexation des rues en les décomposant par région.

Est-ce que c'est possible de faire ça ? Comment puis-je lier ces tables lors de la création de mon index ? Pouvez-vous me donner le code qui va bien, par exemple pour indexer selon la région d'index 1 (et je me débrouillerai pour faire les autres).

Merci par avance,

Bonne journée

Dans un premier temps je voudrais tester les performances avec des jointures.

Car même si les indexes utilisés lors des requêtes deviennent plus petits, si ça nécessite de faire des jointures partout, ça risque d'être contre productif..

Salut
Une rue peut-elle être liée à plus d'une région?
@+

Citation:

---

Envoyé par alassanediakite

Salut
Une rue peut-elle être liée à plus d'une région?
@+

---

Je peux faire en sorte que non

Si une rue est liée à une seule région alors c'est possible de faire un (ou des) index filtré (ou index partiel).
Sinon les vues matérialisées sont la solution. Mais PostgreSQL ne connaitra les vues matérialisées qu'avec la version future (9.3) attendu sous peu.
@+

Citation:

---

Envoyé par alassanediakite

Si une rue est liée à une seule région alors c'est possible de faire un (ou des) index filtré (ou index partiel).
Sinon les vues matérialisées sont la solution. Mais PostgreSQL ne connaitra les vues matérialisées qu'avec la version future (9.3) attendu sous peu.
@+

---

En attendant les vues matérialisées, je vais tenter de faire par index filtrés.

Au hasard, j'ai tenté ceci :

Code:

---

1 2 3 4 5 6 7

CREATE INDEX idx_rues_par_region_29 ON rues, region_rues USING GIST("name" gist_trgm_ops) WHERE region_rues.id_region = 29 AND rues.id = region_rues.id_rue;

---

Mais postgresql n'a pas apprécié.

Peux-tu me donner la syntaxe s'il te plait ?

Merci par avance

Sinon il faut que je crée des tables qui réunissent et sectorisent mes données, puis des indexes dessus.

Par exemple des tables
rues_1
rues_2
rues_3

ou 1 / 2 / 3 sont des identifiants de régions.

Et ensuite je fais des requêtes style

Code:

---

1 2	execute 'select * from rues_' || arg_id_region || ';';

---

En effet, lorsque je mets à jour ces données, j'efface tout pour tout recréer, donc pas besoin de vues qu'elles soient matérialisées ou pas n'est-ce pas ?

Salut
Si tu accepte qu'une rue ne soit liée qu'à une région, alors il faut supprimer la table region_rue et créer une clé étrangère dans la table rue...
rues
- id
- name
- id_region
Le code de création de l'index

Code:

---

1 2 3

CREATE INDEX idx_rues_par_region_29 ON rues(name) WHERE id_region = 29 ;

---

Pourquoi tu utilise un index GIST?
@+

Citation:

---

Envoyé par alassanediakite

Salut
Si tu accepte qu'une rue ne soit liée qu'à une région, alors il faut supprimer la table region_rue et créer une clé étrangère dans la table rue...
rues
- id
- name
- id_region
Le code de création de l'index

Code:

---

1 2 3

CREATE INDEX idx_rues_par_region_29 ON rues(name) WHERE id_region = 29 ;

---

Pourquoi tu utilise un index GIST?
@+

---

J'utilise un index GIST pour utiliser le module trigram

Dans un article sur ce type de recherche textuel via le module trigram, ils utilisent un index GIST, alors j'ai repris bêtement en suivant l'article.

Il se trouve ici : http://bartlettpublishing.com/site/b...og/3/entry/350

Est-ce que btree est préférable ? (j'avoue que je n'y connais pas grand chose).

Pour la structure des tables, j'ai préféré garder quelque chose d'homogène. Ainsi j'ai des tables :
-cities
id
geometry
- city_names
id
id_city
language
name
- city_postcodes
id
id_city
postcode

Et ce pour les pays, les régions, les départements, les villes, les rues, les numéro d'adressage, avec des tables reliant chaque entités entre elles (country_cities, region_cities, county_cities, citiy_streets, streets_numbers etc.).

Le but étant de faire du géocodage (retrouver la position d'un point à partir d'une adresse textuelle donnée). L'algorithme est assez complexe et va nécessiter de multiples liaisons entre ces données, si j'en crois les specs, à priori dans tous les sens. Par conséquent je tenais à garder une structure homogène entre ces entités, indépendante de "comment les requêtes seront construites par la suite" tout en respectant les performances.

Une structure homogène me permettra aussi de pouvoir facilement déboguer le traitement par la suite. Par ailleurs cette structure ne sera pas utilisée uniquement pour faire du géocodage, c'est pourquoi je comptais également la laisser homogène afin que chacun puisse en déduire la structure qui lui va bien pour ses propres besoins.

Ainsi je comptais sur la flexibilité des index, mais apparemment ce n'est pas possible, donc je pense que je vais créer une série de tables intermédiaires de la même manière que la construction de vues, mais où l'on pourra y poser des index. Cela me constituera une couche intermédiaire qui elle sera dédiée à la requête de géocodage, puis dans les traitements futurs, construire une autre couche intermédiaire dédiée à chacun de ces autres traitements.

Sachant que lors des mises à jour cartographique, le tout devra être reconstruit, il n'est pas prévu de modifier par exemple des rues et que le reste des contractions de données se mettent automatiquement à jour, donc je pense que je peux me passer des fonctionnalités premières des vues. Lors des mises à jour cartographiques, il est prévu de tout effacer et de tout reconstruire. A moins qu'il soit possible de créer une vue et y poser des index dessus ?

Donc cette idée de construire des couches intermédiaires via des tables style "region_cities_1", "region_cities_2", "region_cities_3" etc. et y poser des index sur chacune d'entre elles pour ensuite les exploiter de manière dynamique, est-elle crédible ou stupide ?

Etant donné que je ne suis pas expert en base de données, je préfère m'en remettre à vous avant de me lancer dans des opérations plutôt lourdes..

En tout cas merci beaucoup pour vos réponses :)

J'ai oublié de préciser que dans l'exemple des villes, plusieurs noms peuvent exister pour une même ville (Barcelone, Barcelona; Lille, Rijsel; Anvers, Antverpen) ainsi que pour les codes postaux où un même code peut convenir à plusieurs communes et inversement où une commune peut héberger plusieurs codes postaux.

Citation:

---

Envoyé par mister3957

J...ainsi que pour les codes postaux où un même code peut convenir à plusieurs communes et inversement où une commune peut héberger plusieurs codes postaux.

---

Ton schéma ne répond pas à cela!!!
Pour le problème...
Si c'est juste dans le cadre d'apprentissage tu as deux solutions (que je connais):

• utiliser la partition de table
• avec le système de règle (propre à PostgreSQL), utiliser une table comme interface aux autres tables

Sinon (vue le cadre de la BD ie peu de données) un simple index avec une table résout le problème.
@+

Citation:

---

Envoyé par alassanediakite

Ton schéma ne répond pas à cela!!!
Pour le problème...
Si c'est juste dans le cadre d'apprentissage tu as deux solutions (que je connais):

• utiliser la partition de table
• avec le système de règle (propre à PostgreSQL), utiliser une table comme interface aux autres tables

Sinon (vue le cadre de la BD ie peu de données) un simple index avec une table résout le problème.
@+

---

Merci pour ta réponse.

Non le schéma ne correspond pas à ce problème, c'est pourquoi faire des tables pour "transformer" ce schéma actuel pour un qui conviendra mieux à ce problème, sans pour autant péter les possibilités pour les autres problèmes (qui créeront leurs propre schéma à partir du schéma "brut") me semble la meilleure des solutions.

Je voulais juste être sûr que ces schéma intermédiaire ne pouvaient pas se faire via des index, et donc nécessitait la création de nouvelles tables.

Merci pour vos réponses,

A bientôt

Voilà c'est fait !

Finalement j'ai divisé mes tables via un modulo sur des identifiants, modulo que j'ai positionné à 200 concernant les rues (dont la recherche prenait 371 ms sur 461 ms pour un géocodage complet).

L'opération prend 7 minutes mais à la fin, je passe de 461 ms à 121 ms pour un géocodage complet.

Si je généralise aux autres tables, surtout les numéros de maisons qui constituent une table encore plus conséquente que les rues, mais les villes tant qu'à faire, je pense pouvoir obtenir un temps inférieur à 50 ms, bien plus performant que mes objectifs (la technique étant là.. autant l'exploiter à fond).

Reste plus qu'à nettoyer et factoriser, mais le principe est là, testé et approuvé, donc je suis content.

Merci pour vos messages en tout cas :)

A bientôt