Vous n'êtes pas identifié(e).
- Contributions : Récentes | Sans réponse
Pages : 1
#1 Re : Optimisation » Compréhension de l'index spatial » 31/01/2019 11:24:41
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.
#2 Re : Optimisation » Compréhension de l'index spatial » 31/01/2019 00:10:56
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à...
#3 Optimisation » Compréhension de l'index spatial » 30/01/2019 14:59:09
- PleinsDeQuestions
- Réponses : 5
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 !!
Pages : 1