<\/div>

<\/div><\/div>

<\/p>\n

geom && other_geom<\/p>\n

<\/div><\/div>\n\n\n\n

Example:<\/p>\n\n\n

<\/div>

<\/div><\/div>

<\/p>\n

SELECT *
\nFROM routes r
\nJOIN communes c
\nON r.geom && c.geom
\nAND ST_Intersects(r.geom, c.geom);<\/p>\n

<\/div><\/div>\n\n\n\n

The bounding box test is much faster.<\/p>\n\n\n\n

\n\n\n\n

<\/span>4. Check Geometry Complexity<\/span><\/h2>\n\n\n\n
Highly detailed geometries can significantly slow down some operations.<\/p>\n\n\n\n
In some cases, it may be useful to simplify geometries:<\/p>\n\n\n
<\/div>
<\/div>
<\/div><\/div>
<\/p>\n
ST_Simplify(geom, tolerance)<\/p>\n
<\/div><\/div>\n\n\n\n
or to create a simplified table for specific analyses.<\/p>\n\n\n\n
\n\n\n\n
<\/span>5. Avoid Certain Functions in WHERE Clauses<\/span><\/h2>\n\n\n\n
Some functions prevent indexes from being used efficiently.<\/p>\n\n\n\n
For example:<\/p>\n\n\n
<\/div>
<\/div>
<\/div><\/div>
<\/p>\n
ST_Buffer(geom, 100)<\/p>\n
<\/div><\/div>\n\n\n\n
inside a WHERE clause can dramatically slow down a query.<\/p>\n\n\n\n
Bad example:<\/p>\n\n\n
<\/div>
<\/div>
<\/div><\/div>
<\/p>\n
WHERE ST_Intersects(ST_Buffer(geom,100), zone)<\/p>\n
<\/div><\/div>\n\n\n\n
It is often better to rewrite the query.<\/p>\n\n\n\n
\n\n\n\n
<\/span>6. Check Database Statistics<\/span><\/h2>\n\n\n\n
PostgreSQL uses statistics to choose the execution plan.<\/p>\n\n\n\n
After many data modifications, it is recommended to run:<\/p>\n\n\n
<\/div>
<\/div>
<\/div><\/div>
<\/p>\n
ANALYZE<\/p>\n
<\/div><\/div>\n\n\n\n
or<\/p>\n\n\n
<\/div>
<\/div>
<\/div><\/div>
<\/p>\n
VACUUM ANALYZE<\/p>\n
<\/div><\/div>\n\n\n\n
This helps PostgreSQL generate a better query plan.<\/p>\n\n\n\n
\n\n\n\n
<\/span>7. Reduce the Amount of Data Being Processed<\/span><\/h2>\n\n\n\n
It is often useful to reduce the dataset before performing spatial operations.<\/p>\n\n\n\n
For example, using an attribute filter:<\/p>\n\n\n
<\/div>
<\/div>
<\/div><\/div>
<\/p>\n
WHERE type = ‘highway’<\/p>\n
<\/div><\/div>\n\n\n\n
This reduces the number of objects being tested.<\/p>\n\n\n\n
\n\n\n\n
<\/span>8. Test Different Approaches<\/span><\/h2>\n\n\n\n
In some cases, different query formulations can produce very different performance results.<\/p>\n\n\n\n
It is therefore useful to test several strategies:<\/p>\n\n\n\n
\n
spatial joins<\/li>\n\n\n\n
subqueries<\/li>\n\n\n\n
intermediate materialization.<\/li>\n<\/ul>\n\n\n\n
\n\n\n\n
<\/span>9. The 5 Most Common PostGIS Mistakes That Slow Down Queries<\/span><\/h2>\n\n\n\n
Certain mistakes appear very frequently in spatial databases.<\/p>\n\n\n\n
<\/span>Missing Spatial Index<\/span><\/h3>\n\n\n\n
This is the most common cause.<\/p>\n\n\n\n
Without a spatial index, PostgreSQL must scan every geometry in the table.<\/p>\n\n\n\n
Create an index:<\/p>\n\n\n
<\/div>
<\/div>
<\/div><\/div>
<\/p>\n
CREATE INDEX idx_routes_geom
\nON routes
\nUSING GIST (geom);<\/p>\n
<\/div><\/div>\n\n\n\n
\n\n\n\n
<\/span>Poor Order of Operations<\/span><\/h3>\n\n\n\n
Some queries immediately execute an expensive spatial operation on the entire table.<\/p>\n\n\n\n
Inefficient example:<\/p>\n\n\n
<\/div>
<\/div>
<\/div><\/div>
<\/p>\n
SELECT *
\nFROM buildings
\nWHERE ST_Intersects(geom, ST_Buffer(:zone,100));<\/p>\n
<\/div><\/div>\n\n\n\n
In this case, the ST_Buffer function is recalculated for every row.<\/p>\n\n\n\n
\n\n\n\n
<\/span>Spatial Function Used Without a Spatial Filter<\/span><\/h3>\n\n\n\n
A spatial join can be very slow if no bounding box filter is used.<\/p>\n\n\n\n
Recommended example:<\/p>\n\n\n
<\/div>
<\/div>
<\/div><\/div>
<\/p>\n
SELECT *
\nFROM routes r
\nJOIN communes c
\nON r.geom && c.geom
\nAND ST_Intersects(r.geom, c.geom);<\/p>\n
<\/div><\/div>\n\n\n\n
The && operator uses the spatial index.<\/p>\n\n\n\n
\n\n\n\n
<\/span>Overly Complex Geometries<\/span><\/h3>\n\n\n\n
Some geometries contain thousands of vertices.<\/p>\n\n\n\n
In some cases, it may be useful to use:<\/p>\n\n\n
<\/div>
<\/div>
<\/div><\/div>
<\/p>\n
ST_Simplify(geom, tolerance)<\/p>\n
<\/div><\/div>\n\n\n\n
or store a simplified version.<\/p>\n\n\n\n
\n\n\n\n
<\/span>Outdated PostgreSQL Statistics<\/span><\/h3>\n\n\n\n
If the table has been heavily modified, PostgreSQL may choose a poor execution plan.<\/p>\n\n\n\n
Update statistics:<\/p>\n\n\n
<\/div>
<\/div>
<\/div><\/div>
<\/p>\n
VACUUM ANALYZE<\/p>\n
<\/div><\/div>\n\n\n\n
\n\n\n\n
<\/span>Real Optimization Example<\/span><\/h2>\n\n\n\n
Suppose we want to find all roads inside a municipality.<\/p>\n\n\n\n
Initial query:<\/p>\n\n\n
<\/div>
<\/div>
<\/div><\/div>
<\/p>\n
SELECT r.*
\nFROM routes r, communes c
\nWHERE ST_Intersects(r.geom, c.geom)
\nAND c.nom = ‘Paris’;<\/p>\n
<\/div><\/div>\n\n\n\n
Execution time: 45 seconds<\/p>\n\n\n\n
<\/span>Optimized Version<\/span><\/h3>\n\n\n
<\/div>
<\/div>
<\/div><\/div>
<\/p>\n
SELECT r.*
\nFROM routes r
\nJOIN communes c
\nON r.geom && c.geom
\nAND ST_Intersects(r.geom, c.geom)
\nWHERE c.nom = ‘Paris’;<\/p>\n
<\/div><\/div>\n\n\n\n
With:<\/p>\n\n\n\n
\n
spatial index<\/li>\n\n\n\n
bounding box filter<\/li>\n<\/ul>\n\n\n\n
Execution time: less than one second<\/p>\n\n\n\n
\n\n\n\n
<\/span>Quick Checklist for Diagnosing a Slow PostGIS Query<\/span><\/h2>\n\n\n\n
When a query takes too long, check:<\/p>\n\n\n\n
✔ the query using EXPLAIN ANALYZE
✔ the presence of a spatial index
✔ the use of index-compatible operators
✔ geometry complexity
✔ the presence of an attribute filter
✔ PostgreSQL statistics (ANALYZE)<\/p>\n\n\n\n
\n\n\n\n
<\/span>Conclusion<\/span><\/h2>\n\n\n\n
When a PostGIS query is slow, it is important to follow a step-by-step optimization process:<\/p>\n\n\n\n
\n
analyze the query with EXPLAIN ANALYZE<\/li>\n\n\n\n
verify spatial indexes<\/li>\n\n\n\n
use index-compatible operators<\/li>\n\n\n\n
simplify geometries when necessary<\/li>\n\n\n\n
maintain up-to-date database statistics.<\/li>\n<\/ul>\n\n\n\n
With these simple steps, it is often possible to reduce execution times from several minutes to just a few seconds.<\/p>\n\n\n\n
In most cases, a slow PostGIS query is caused by a missing index or a poor execution plan. A methodical analysis using EXPLAIN ANALYZE will almost always help identify the problem.<\/p>\n\n\n\n
\n","protected":false},"excerpt":{"rendered":"
Spatial databases can contain millions of geographic objects. In some cases, a PostGIS query may take several seconds \u2014 or even several minutes \u2014 to execute. This problem often occurs when: In this article, we will…<\/p>\n","protected":false},"author":1,"featured_media":16770,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"give_campaign_id":0,"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"sfsi_plus_gutenberg_text_before_share":"","sfsi_plus_gutenberg_show_text_before_share":"","sfsi_plus_gutenberg_icon_type":"","sfsi_plus_gutenberg_icon_alignemt":"","sfsi_plus_gutenburg_max_per_row":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[1379,3264],"tags":[4645,1349],"class_list":["post-16760","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","category-postgis-fr-es-en","tag-optimization","tag-postgis-en"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.sigterritoires.fr\/wp-content\/uploads\/2026\/05\/postgis2-2.png?fit=745%2C331&ssl=1","jetpack_shortlink":"https:\/\/wp.me\/p6XU0A-4mk","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/16760","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/comments?post=16760"}],"version-history":[{"count":0,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/16760\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/media\/16770"}],"wp:attachment":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/media?parent=16760"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/categories?post=16760"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/tags?post=16760"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

<\/span>1. Start by Analyzing the Query<\/span><\/h2>\n\n\n\nThe first step is understanding how PostgreSQL executes the query.<\/p>\n\n\n\nFor this, we use:<\/p>\n\n\n<\/div>