Chapitre 6. Conseils sur les performances

Current PostgreSQL versions suffer from a query optimizer weakness regarding TOAST tables. TOAST tables are a kind of "extension room" used to store large (in the sense of data size) values that do not fit into normal data pages (like long texts, images or complex geometries with lots of vertices), see the PostgreSQL Documentation for TOAST for more information).

Le problème apparaît s'il vous arrive d'avoir une table avec d'assez grandes géométries, mais pas beaucoup de lignes d'entre elles (comme un tableau contenant les frontières de tous les pays européens en haute résolution). Ensuite, le tableau lui-même est petit, mais il utilise beaucoup d'espace TOAST. Dans notre exemple, le cas, la table elle-même avait environ 80 lignes et seulement 3 pages de données utilisées, mais la table TOAST 8225 pages utilisé.

Maintenant émettre une requête en utilisant l'opérateur de géométrie && pour rechercher une boîte englobante qui correspond que très peu de ces lignes. Maintenant l'optimiseur de requêtes voit que la table n'a que 3 pages et 80 lignes. Il estime qu'une analyse séquentielle sur une telle petite table est beaucoup plus rapide que d'utiliser un index. Et alors il décide d'ignorer l'index GIST. Habituellement, cette estimation est correcte. Mais dans notre cas, l'opérateur && doit aller chercher chaque géométrie à partir du disque pour comparer les boîtes englobantes, et par conséquent la lecture de toutes les pages TOAST également.

Pour voir si vous souffrez de ce problème, utilisez la commande postgresql "EXPLAIN ANALYZE". Pour plus d'informations et les détails techniques, vous pouvez lire le fil de discussion sur la liste de diffusion sur les performances de PostgreSQL : http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php

et un fil d'actualités plus récent sur PostGIS https://lists.osgeo.org/pipermail/postgis-devel/2017-June/026209.html

Les personnes de PostgreSQL essayent de résoudre ce problème en faisant l'estimation de la requête TOAST-courant. Pour l'instant, voici deux solutions :

La première solution consiste à forcer le planificateur de requêtes à utiliser l'index. Envoyer "SET enable_seqscan TO off;" au serveur avant d'émettre la requête. Cela force le planificateur de requêtes à éviter balayages séquentiels lorsque cela est possible. Donc, il utilise l'index GIST comme d'habitude. Mais cet indicateur doit être fixé à chaque connexion, et il provoque le planificateur de requêtes à faire des erreurs d'estimation dans les autres cas, vous devrez donc faire "SET POUR enable_seqscan sur;" après la requête.

La deuxième solution consiste à faire le balayage séquentielle aussi vite que le planificateur de requêtes pense. Ceci peut être réalisé en créant une colonne supplémentaire qui "cache" la bbox, et contre cette correspondance. Dans notre exemple, les commandes sont comme :

SELECT AddGeometryColumn('myschema', 'mytable', 'bbox', '4326', 'GEOMETRY', '2');
UPDATE mytable SET bbox = ST_Envelope(ST_Force2D(geom));

Modifiez maintenant votre requête pour utiliser l'opérateur && contre bbox au lieu de geom_column, comme suit :

SELECT geom_column
FROM mytable
WHERE bbox && ST_SetSRID('BOX3D(0 0,1 1)'::box3d,4326);

Bien sûr, si vous changez ou ajoutez des lignes à mytable, vous devez garder la bbox "synchro". La façon la plus transparente pour ce faire serait des déclencheurs, mais vous pouvez également modifier votre application afin de maintenir la colonne bbox courante ou exécuter la requête UPDATE ci-dessus après chaque modification.