Vous n'êtes pas identifié(e).
Pages : 1
Bonjour,
Je suis actuellement sur un développement cartographique, je travaille sur une base avec l'extension PostGIS et je rencontre un problème avec ma compréhension des index spatiaux.
Pour information, je ne suis pas DBA, j'ai juste quelques connaissances, aussi je pense que je passe à côté de quelque chose donc si vous pouvez m'aiguiller ça m'aiderait beaucoup ! 
Je ne peux pas mettre ma base à disposition néanmoins j'ai pu reproduire le problème avec une base trouvée sur le net donc si vous voulez essayer... 
Sur le site de Natural Earth, j'ai téléchargé la base "Download North Americ supplement".
Une fois chargée, j'ai exécuté les commandes suivantes :
SELECT UpdateGeometrySRID('ne_10m_roads_north_america', 'geom', 4326);
CREATE INDEX idx_geom ON ne_10m_roads_north_america USING GIST(geom);
VACUUM (FULL, FREEZE, ANALYZE, VERBOSE) ne_10m_roads_north_america;Suit les commandes avec leurs plans d'exécution.
Exécution initiale :
EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING, SUMMARY, VERBOSE) SELECT * FROM ne_10m_roads_north_america WHERE ST_Intersects(ST_GeomFromText('POLYGON((-165 75, -165 10, -40 10, -40 75, -165 75))', 4326), geom);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..15089.79 rows=16394 width=1437) (actual time=4.988..338.215 rows=49172 loops=1)
Output: gid, prefix, number, class, type, divided, country, state, note, scalerank, uident, length, rank, continent, geom
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=16277
-> Parallel Seq Scan on public.ne_10m_roads_north_america (cost=0.00..12450.39 rows=6831 width=1437) (actual time=0.078..77.799 rows=16391 loops=3)
Output: gid, prefix, number, class, type, divided, country, state, note, scalerank, uident, length, rank, continent, geom
Filter: (('0103000020E610000001000000050000000000000000A064C00000000000C052400000000000A064C0000000000000244000000000000044C0000000000000244000000000000044C00000000000C052400000000000A064C00000000000C05240'::geometry && ne_10m_roads_north_america.geom) AND _st_intersects('0103000020E610000001000000050000000000000000A064C00000000000C052400000000000A064C0000000000000244000000000000044C0000000000000244000000000000044C00000000000C052400000000000A064C00000000000C05240'::geometry, ne_10m_roads_north_america.geom))
Rows Removed by Filter: 4
Buffers: shared hit=16277
Worker 0: actual time=0.059..39.364 rows=9341 loops=1
Buffers: shared hit=3364
Worker 1: actual time=0.101..54.351 rows=9456 loops=1
Buffers: shared hit=3464
Planning time: 0.432 ms
Execution time: 340.440 ms
(16 rows)Suppression de l'utilisation du scan séquentiel puis exécution de la même requête :
SET enable_seqscan TO OFF;
EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING, SUMMARY, VERBOSE) SELECT * FROM ne_10m_roads_north_america WHERE ST_Intersects(ST_GeomFromText('POLYGON((-165 75, -165 10, -40 10, -40 75, -165 75))', 4326), geom);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=2721.25..16811.04 rows=16394 width=1437) (actual time=22.936..354.417 rows=49172 loops=1)
Output: gid, prefix, number, class, type, divided, country, state, note, scalerank, uident, length, rank, continent, geom
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=12042
-> Parallel Bitmap Heap Scan on public.ne_10m_roads_north_america (cost=1721.25..14171.64 rows=6831 width=1437) (actual time=8.743..73.247 rows=16391 loops=3)
Output: gid, prefix, number, class, type, divided, country, state, note, scalerank, uident, length, rank, continent, geom
Recheck Cond: ('0103000020E610000001000000050000000000000000A064C00000000000C052400000000000A064C0000000000000244000000000000044C0000000000000244000000000000044C00000000000C052400000000000A064C00000000000C05240'::geometry && ne_10m_roads_north_america.geom)
Filter: _st_intersects('0103000020E610000001000000050000000000000000A064C00000000000C052400000000000A064C0000000000000244000000000000044C0000000000000244000000000000044C00000000000C052400000000000A064C00000000000C05240'::geometry, ne_10m_roads_north_america.geom)
Heap Blocks: exact=3595
Buffers: shared hit=12042
Worker 0: actual time=5.048..50.006 rows=12267 loops=1
Buffers: shared hit=2753
Worker 1: actual time=3.993..50.751 rows=12197 loops=1
Buffers: shared hit=3077
-> Bitmap Index Scan on idx_geom (cost=0.00..1717.15 rows=49183 width=0) (actual time=14.625..14.625 rows=49172 loops=1)
Index Cond: ('0103000020E610000001000000050000000000000000A064C00000000000C052400000000000A064C0000000000000244000000000000044C0000000000000244000000000000044C00000000000C052400000000000A064C00000000000C05240'::geometry && ne_10m_roads_north_america.geom)
Buffers: shared hit=337
Planning time: 0.494 ms
Execution time: 357.957 ms
(20 rows)Suppression de l'utilisation du bitmap index puis exécution de la même requête :
SET enable_bitmapscan TO OFF;
EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING, SUMMARY, VERBOSE) SELECT * FROM ne_10m_roads_north_america WHERE ST_Intersects(ST_GeomFromText('POLYGON((-165 75, -165 10, -40 10, -40 75, -165 75))', 4326), geom);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_geom on public.ne_10m_roads_north_america (cost=0.28..42788.73 rows=16394 width=1437) (actual time=0.156..208.141 rows=49172 loops=1)
Output: gid, prefix, number, class, type, divided, country, state, note, scalerank, uident, length, rank, continent, geom
Index Cond: ('0103000020E610000001000000050000000000000000A064C00000000000C052400000000000A064C0000000000000244000000000000044C0000000000000244000000000000044C00000000000C052400000000000A064C00000000000C05240'::geometry && ne_10m_roads_north_america.geom)
Filter: _st_intersects('0103000020E610000001000000050000000000000000A064C00000000000C052400000000000A064C0000000000000244000000000000044C0000000000000244000000000000044C00000000000C052400000000000A064C00000000000C05240'::geometry, ne_10m_roads_north_america.geom)
Buffers: shared hit=28859
Planning time: 0.296 ms
Execution time: 210.150 ms
(7 rows)Je constate alors que l'utilisation de l'index serait le plus profitable en terme de temps d'exécution (340.440 ms avec seq scan - 357.957 ms avec bitmap index scan - 210.150 ms avec index scan).
Questions :
1/ Je crois comprendre que le planificateur a choisit le plan seq scan car le coût est de 15089.79 alors que pour le bitmap index scan, le coût est de 16811.04 et pour l'index scan de 42788.73. Est-ce bien pour cela ? Sinon quels sont les critères de choix du plan ?
En fait, je constate, sur ma base de travail, qui n'est pas celle de cet exemple, que le planificateur se trompe souvent; parfois, il utilise l'index scan alors que je constate que le seq scan est plus rapide néanmoins le coût est, il est vrai, plus élevé (cela va de ~5 min avec index scan à ~30s avec seq scan, la différence est donc assez importante).
2/ Comment influencer le planificateur quand il s'agit de colonne géométrique ? (sans passer par du SET enable_... TO OFF;)
Pour information, j'ai fait des tests en modifiant les statistiques (ALTER TABLE .... SET STATISTICS...) et cela n'a pas changer le résultat (aussi bien sur cette base de tests que sur ma base de travail).
3/ Je constate églement que le nombre de lignes estimés est largement différent du nombre de lignes réels; est-ce important ? Comment influencer cela ?
Bref, j'ai vraiment l'impression que le planificateur n'est pas efficace quand il s'agit d'une colonne géométrique (alors que pour les colonnes "classiques", je ne rencontre pas ce soucis). J'aimerais comprendre ce qu'il se passe
Si vous avez des suggestions, je suis très preneur !!
Hors ligne
1/ Je crois comprendre que le planificateur a choisit le plan seq scan car le coût est de 15089.79 alors que pour le bitmap index scan, le coût est de 16811.04 et pour l'index scan de 42788.73. Est-ce bien pour cela ? Sinon quels sont les critères de choix du plan ?
C'est bien ça. Le calcul du coût lui indique que le parcours séquentiel serait le moins coûteux, donc il passe par ce dernier.
En fait, je constate, sur ma base de travail, qui n'est pas celle de cet exemple, que le planificateur se trompe souvent; parfois, il utilise l'index scan alors que je constate que le seq scan est plus rapide néanmoins le coût est, il est vrai, plus élevé (cela va de ~5 min avec index scan à ~30s avec seq scan, la différence est donc assez importante).
En effet. Autant la différence sur les plans montrés est très légère, autant là, de 5 minutes à 30 secondes, c'est problématique.
2/ Comment influencer le planificateur quand il s'agit de colonne géométrique ? (sans passer par du SET enable_... TO OFF;)
Pour information, j'ai fait des tests en modifiant les statistiques (ALTER TABLE .... SET STATISTICS...) et cela n'a pas changer le résultat (aussi bien sur cette base de tests que sur ma base de travail).
Il n'y a pas de moyen d'influencer le planificateur spécifiquement pour des colonnes géométriques. On peut l'influencer via la commande ALTER TABLE colonne par colonne, mais pas pour un type de données.
3/ Je constate églement que le nombre de lignes estimés est largement différent du nombre de lignes réels; est-ce important ? Comment influencer cela ?
Je ne les trouve pas si différent que ça. En effet, cela va du simple au double, mais dans ce cas, ça veut dire une différence de 20000 sur 40000. Avoir une meilleure statistique ne changera probablement pas le plan. Encore une fois, je pense que l'exemple proposé n'est pas très parlant et il me semble là très difficile de l'améliorer pour un gain très relatif (on parle, sur ces plans, de passer de 338ms à 208ms).
Guillaume.
Hors ligne
Merci beaucoup pour la réponse.
Effectivement pour cet exemple-ci, la différence au niveau du temps d'exécution est trop faible pour s'en préoccuper; en fait, via cet exemple, je souhaitais juste montrer que le plan choisit n'était, dans la pratique, pas le meilleur et je ne comprends pas comment modifier cela.
Je n'ai pas compris ce que tu veux dire par "On peut l'influencer via la commande ALTER TABLE colonne par colonne, mais pas pour un type de données."; est-ce que tu pourrais être plus précis, stp ?
Dans ce cas-ci, tu dis que modifier les statistiques ne changeraient pas le choix du planificateur; ok, je le constate mais d'une manière générale, je crois comprendre que ça pourrait être le cas, non ? Puisque le planificateur s'appuie sur les statistiques.
Je viens de refaire vite fait un autre test sur une table contenant 10 000 000 de lignes et j'ai un résultat que je ne comprends pas.
Exécution initiale :
EXPLAIN (COSTS, SUMMARY, VERBOSE) SELECT * FROM tab_geom_avec_index_gist WHERE ST_Intersects(ST_GeomFromText('POLYGON((0 45, 0 47, 5 47, 5 45, 0 45))', 4326), wkb_polygon);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_poly_gist on public.tab_geom_avec_index_gist (cost=0.42..145.25 rows=11 width=284)
Output: id, wkb_point, wkb_polygon, wkt_point, wkt_polygon
Index Cond: ('0103000020E610000001000000050000000000000000000000000000000080464000000000000000000000000000804740000000000000144000000000008047400000000000001440000000000080464000000000000000000000000000804640'::geometry && tab_geom_avec_index_gist.wkb_polygon)
Filter: _st_intersects('0103000020E610000001000000050000000000000000000000000000000080464000000000000000000000000000804740000000000000144000000000008047400000000000001440000000000080464000000000000000000000000000804640'::geometry, tab_geom_avec_index_gist.wkb_polygon)
Planning time: 0.200 ms
(5 rows)Suppression de l'utilisation de l'index scan puis exécution de la même requête :
SET enable_indexscan TO OFF;
EXPLAIN (COSTS, SUMMARY, VERBOSE) SELECT * FROM tab_geom_avec_index_gist WHERE ST_Intersects(ST_GeomFromText('POLYGON((0 45, 0 47, 5 47, 5 45, 0 45))', 4326), wkb_polygon);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.tab_geom_avec_index_gist (cost=4.67..144.42 rows=11 width=284)
Output: id, wkb_point, wkb_polygon, wkt_point, wkt_polygon
Recheck Cond: ('0103000020E610000001000000050000000000000000000000000000000080464000000000000000000000000000804740000000000000144000000000008047400000000000001440000000000080464000000000000000000000000000804640'::geometry && tab_geom_avec_index_gist.wkb_polygon)
Filter: _st_intersects('0103000020E610000001000000050000000000000000000000000000000080464000000000000000000000000000804740000000000000144000000000008047400000000000001440000000000080464000000000000000000000000000804640'::geometry, tab_geom_avec_index_gist.wkb_polygon)
-> Bitmap Index Scan on idx_poly_gist (cost=0.00..4.67 rows=33 width=0)
Index Cond: ('0103000020E610000001000000050000000000000000000000000000000080464000000000000000000000000000804740000000000000144000000000008047400000000000001440000000000080464000000000000000000000000000804640'::geometry && tab_geom_avec_index_gist.wkb_polygon)
Planning time: 0.295 ms
(7 rows)Suppression de l'utilisation du bitmap index scan puis exécution de la même requête :
SET enable_bitmapscan TO OFF;
EXPLAIN (COSTS, SUMMARY, VERBOSE) SELECT * FROM tab_geom_avec_index_gist WHERE ST_Intersects(ST_GeomFromText('POLYGON((0 45, 0 47, 5 47, 5 45, 0 45))', 4326), wkb_polygon);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..1489423.10 rows=11 width=284)
Output: id, wkb_point, wkb_polygon, wkt_point, wkt_polygon
Workers Planned: 2
-> Parallel Seq Scan on public.tab_geom_avec_index_gist (cost=0.00..1488422.00 rows=5 width=284)
Output: id, wkb_point, wkb_polygon, wkt_point, wkt_polygon
Filter: (('0103000020E610000001000000050000000000000000000000000000000080464000000000000000000000000000804740000000000000144000000000008047400000000000001440000000000080464000000000000000000000000000804640'::geometry && tab_geom_avec_index_gist.wkb_polygon) AND _
st_intersects('0103000020E610000001000000050000000000000000000000000000000080464000000000000000000000000000804740000000000000144000000000008047400000000000001440000000000080464000000000000000000000000000804640'::geometry, tab_geom_avec_index_gist.wkb_polygon))
Planning time: 0.154 ms
(7 rows)Et là, la question est tout simplement :
4/ Pourquoi le planificateur a choisit l'index scan lors de l'exécution "normale" (sans modification de "enable_...") avec un coût de 145.25 alors que le bitmap index scan à un coût de 144.42 donc inférieur ?
Voilà, voilà...
Hors ligne
Je n'ai pas compris ce que tu veux dire par "On peut l'influencer via la commande ALTER TABLE colonne par colonne, mais pas pour un type de données."; est-ce que tu pourrais être plus précis, stp ?
Comme tu l'as indiqué dans ton premier message, le ALTER TABLE permet de changer la taille de l'échantillon statistique pris, mais cela se fait pour une colonne particulière (ALTER TABLE... ALTER COLUMN... STATISTICS...). On ne peut pas changer cet échantillon statistique pour un type particulier (varchar par exemple, ou polygon). Je n'ai pas de ALTER TYPE pour ça. On peut écrire un script qui va changer le STATISTICS pour chaque colonne de type X dans la base, mais on ne peut pas le faire de façon globale.
Dans ce cas-ci, tu dis que modifier les statistiques ne changeraient pas le choix du planificateur; ok, je le constate mais d'une manière générale, je crois comprendre que ça pourrait être le cas, non ? Puisque le planificateur s'appuie sur les statistiques.
Oui, c'est le but des statistiques. Plus les statistiques sont fines, plus le nombre de lignes estimées est précis et meilleur sera le plan. Mais cela vaut quand l'erreur d'estimation est importante ie (1000 lignes par rapport à 1 million par exemple).
4/ Pourquoi le planificateur a choisit l'index scan lors de l'exécution "normale" (sans modification de "enable_...") avec un coût de 145.25 alors que le bitmap index scan à un coût de 144.42 donc inférieur ?
J'ai eu le même problème chez un client. En fait, lorsque l'optimiseur de requêtes crée les différents plans et compare les coûts, il ne le fait pas exactement. Ce n'est pas une comparaison stricte. Il s'accorde un delta de +1%. L'algo utilisé n'est pas très complexe. Les deux plans sont considérés identiques si leur coût (initial et final) avec le delta sont identiques. Un plan est préféré à un autre si son coût initial est meilleur et que le coût final avec delta est identique. Ça correspond à ton cas. Le coût initial avec l'Index Scan est de 0.42 alors qu'avec le Bitmap Scan, on est sur 4.67. Quant au coût, on a 145.25 pour le premier et 144.42 pour le second. Le premier (donc l'Index Scan) l'emporte.
Tu trouveras plus de détails sur https://github.com/postgres/postgres/bl … ode.c#L141.
Guillaume.
Hors ligne
En résumé, y a pas vraiment grand chose à faire pour mon cas de figure... Dommage !
Quoiqu'il en soit, un énorme merci pour tout tes éclairages !
Le lien vers le commentaire pour comprendre le choix d'un plan par rapport à un autre est très intéressant.
Dernière modification par PleinsDeQuestions (31/01/2019 11:37:03)
Hors ligne
Je ne dis pas qu'il n'y a rien à faire en général dans votre cas. Mais sur les deux exemples montrés, en effet, on ne pourra rien faire.
Guillaume.
Hors ligne
Pages : 1