Les versions actuelles de PostgreSQL (dont 8.0) souffrent d'un défaut de l'optimiseur de requêtes en ce qui concerne les tables TOAST. Les tables TOAST sont une sorte
d'"espace supplémentaire" servant à stocker de "grandes" valeurs (grandes au sens de la taille des données) qui ne peuvent loger dans des pages de données normales (comme les longs textes, les images, ou les géométries complexes comportant un grand nombre d'arcs), voir http://www.postgresql.org/docs/8.0/static/storage-toast.html pour plus d'information).
Le problème apparait si vous possédez une table avec des géométries de taille importantes, mais avec peu de tuples (par exemple une table contenant les frontières des pays européens en haute résolution). La table elle-même est relativement petite, mais elle utilise énormément d'espace TOAST. Dans notre exemple, la table compte environ 80 lignes (ou tuples), et n'utilise que 3 pages de données, mais la table TOAST utilise 8225 pages.
A présent, essayez de lancer une requête en utilisant l'opérateur géométrique && pour rechercher une boite englobante qui ne comprend que quelques-uns de ces tuples.
L'optimiseur de requête constate que votre table ne compte que 3 pages et 80 lignes. Il estime par conséquent qu'un scan séquentiel sur une si petite table est beaucoup plus rapide que d'utiliser un index. Et il choisir d'ignorer l'index GIST. D'ordinaire, son estimation est juste mais dans notre cas, l'opérateur && doit aller récupérer chaque objet géométrique sur le disque pour comparer les boites englobantes, et il parcoure par la même occasion toutes les pages TOAST.
Pour vérifier si vous pâtissez de ce bogue, utilisez la commande "EXPLAIN ANALYZE" de postgresql. Pour plus d'informations et des détails techniques, vous pouvez lire ce sujet sur la liste de discussion des performances de postgres : http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php

Les développeurs de PostgreSQL essayent de résoudre ce problème en faisant en sorte que l'estimateur de la requête prenne en considération l'espace TOAST. Pour le moment, voici deux solutions de contournement :

La première est de forcer le préparateur de requête à utiliser l'index. Envoyez "SET enable_seqscan TO off;" au serveur avant de lancer la requête. Cela force le préparateur de requête à éviter les scans séquentiels intempestifs lorsque c'est possible. Il utilise alors l'index GIST comme d'habitude. Cependant, ce drapeau doit être fixé à chaque connexion, et il conduit le préparateur de requête à faire des erreurs d'estimation dans d'autres cas de figure, il faut donc le remettre à "SET enable_seqscan TO on;" après la requête.

La seconde solution est de rendre le scan séquentiel aussi rapide que le préparateur de requête le pense. Cela peut se faire en créant un champ additionnel qui met la boîte englobante en cache, et de se servir de ce nouveau champ pour opérer les comparaisons. Dans notre exemple, les commandes sont :

Maintenant, changez votre requête en utilisant l'opérateur && sur la boîte englobante à la place de geom_column, comme suit:

Bien entendu, si vous changez ou ajoutez des lignes à mytable, vous devrez veiller à ce que la boîte englobante reste à jour. La façon la plus transparente de faire cela est par une moulinette automatique, mais vous pouvez aussi modifier votre application pour que le champ de la boîte englobante reste à jour de façon permanente ou lancer la requête UPDATE après chaque modification.

Pour des tables qui sont essentiellement en lecture seule et où un simple index sert à la majorité des requêtes, PostgreSQL offre la commande CLUSTER. Cette commande réordonne physiquement toutes les lignes de données dans l'ordre du critère de l'index, ce qui offre deux avantages du point de vue des performances. D'abord pour les scans d'index, le nombre de recherche dans la table est considérablement réduit. Deuxièmement, si votre ensemble de travaille ne concerne que de petits intervalles sur l'index, vous obtenez une recherche plus efficace car les données sont dispersées sur moins de pages. (Merci de vous référer à la documentation de la commande CLUSTER du manuel PostgreSQL pour davantage d'informations).

Cependant, PostgreSQL ne permet actuellement pas le clustering sur les index GIST de PostGIS car les index GIST ignorent les valeurs NULL, vous obtenez un message d'erreur du type :

Comme le message vous le spécifie, on peut contourner cet inconvénient en ajoutant une contrainte NOT NULLl à la table :

Bien sûr, cela ne fonctionnera pas si vous avez de fait besoin de valeurs NULL dans votre colonne de géométrie. En plus vous devez donc utiliser la méthode ci-dessus pour ajouter la contrainte, utiliser une vérification du style ALTER TABLE blubb ADD CHECK (geometry is not null); ne fonctionnera pas.

Il arrive quelquefois que vous ayez des données 3D ou 4D dans votre table, mais vous n'y accéder qu'au travers de fonctions conformes aux standards de l'OpenGIS telles que Text() ou Binary() qui ne fournissent en sortie que des données géométriques 2D. Ces fonctions utilisent en effet un appel à la fonction force_2d(), qui induit une perte de temps pour les géométries de grandes tailles. Pour l'éviter, vous pouvez au préalable vous débarrasser des dimensions superflues une bonne fois pour toutes en exécutant :

Veuillez noter que si vous avez ajouter votre champ géométrique au moyen de AddGeometryColumn() vous subirez une contrainte sur la dimension géométrique.

Pour la dépasser, vous devrez d'abord lever la contrainte. Rappelez-vous ensuite de mettre à jour l'entrée de la table geometry_columns et de recréer par la suite la contrainte.

Dans le cas de manipulation de grandes tables, il est préférable de diviser l'UPDATE entre plus petites parties en restreignant l'UPDATE à une partie de la table au moyen d'une clause WHERE et de votre clef primaire, ou d'un autre critère quelconque, et d'exécuter un simple nétoyage ("VACUUM;") entre vos UPDATEs. Cela réduit votre besoin temporaire en espace disque de manière radicale. De plus, si vous avez des données géométriques avec différentes dimensions, restreindre l'UPDATE à "WHERE dimension(the_geom)>2" vous évite de réécrire les géométries qui sont d'ores et déjà en 2D.