2

I have this SQL query to get roads from my PostGIS server.

WITH boxed_roads AS 
(SELECT * FROM "planet_osm_line" 
WHERE ST_Within(way, ST_MakeEnvelope(xmax ,xmin, ymax, ymin, 4326)) 
)

SELECT jsonb_build_object('type', 'FeatureCollection', 
'features', json_agg(ST_AsGeoJSON(json_res)::jsonb)) FROM 
(SELECT * FROM boxed_roads 
WHERE (highway = 'motorway' or highway = 'trunk' or 
highway = 'secondary' or highway = 'primary' or 
highway = 'unclassified' or highway = 'residential' or highway = 'tertiary' 
or highway = 'track' or highway = 'service') 
ORDER BY ST_Length(way) Desc LIMIT 200) as json_res;

I'm trying to increase the efficiency of this postGIS query as it is taking on average 350ms and that seems too much. Anything I should change query-wise?

I've been reading the PostGIS Documentation and found talk about indexes but haven't found a way to manually configure to use them. I guess it happens within geospatial functions like ST_Within.

Also, this is OSM data and each row is filled with None value properties.

Did anyone have the same issue? I found questions here about unions and joins but I only want to query. The information in this DB is static and will probably never change until I update it at some point. No handlers will make any changes to it.

This is my first GIS related question so if you need me to add more information, let me know. This DB is PostgreSQL 12 with PostGIS 3+ I'm running this query with psycopg2 on python 3.8.1.

Here's the EXPLAIN ANALYZE output:

"Aggregate  (cost=54725.32..54725.33 rows=1 width=32) (actual time=359.728..359.730 rows=1 loops=1)"
"  ->  Subquery Scan on json_res  (cost=54686.90..54699.56 rows=100 width=1131) (actual time=236.965..252.877 rows=100 loops=1)"
"        ->  Limit  (cost=54686.90..54698.56 rows=100 width=1115) (actual time=236.934..250.602 rows=100 loops=1)"
"              ->  Gather Merge  (cost=54686.90..60576.64 rows=50480 width=1115) (actual time=236.926..250.370 rows=100 loops=1)"
"                    Workers Planned: 2"
"                    Workers Launched: 2"
"                    ->  Sort  (cost=53686.87..53749.97 rows=25240 width=1115) (actual time=226.442..226.520 rows=57 loops=3)"
"                          Sort Key: (st_length(planet_osm_line.way)) DESC"
"                          Sort Method: top-N heapsort  Memory: 197kB"
"                          Worker 0:  Sort Method: top-N heapsort  Memory: 272kB"
"                          Worker 1:  Sort Method: top-N heapsort  Memory: 235kB"
"                          ->  Parallel Bitmap Heap Scan on planet_osm_line  (cost=252.45..52722.22 rows=25240 width=1115) (actual time=20.177..192.332 rows=30106 loops=3)"
"                                Filter: ((highway = ANY ('{motorway,trunk,secondary,primary,unclassified,residential,tertiary,track,service}'::text[])) AND st_within(way, '0103000020E61000000100000005000000E14830254D515DC013CFFFB8E3465DC0E14830254D515DC0EE5807D4D26C4040FCD56DF3B5674040EE5807D4D26C4040FCD56DF3B567404013CFFFB8E3465DC0E14830254D515DC013CFFFB8E3465DC0'::geometry))"
"                                Rows Removed by Filter: 6318"
"                                Heap Blocks: exact=1117"
"                                ->  Bitmap Index Scan on planet_osm_line_index  (cost=0.00..237.31 rows=3319 width=0) (actual time=28.226..28.226 rows=109272 loops=1)"
"                                      Index Cond: (way @ '0103000020E61000000100000005000000E14830254D515DC013CFFFB8E3465DC0E14830254D515DC0EE5807D4D26C4040FCD56DF3B5674040EE5807D4D26C4040FCD56DF3B567404013CFFFB8E3465DC0E14830254D515DC013CFFFB8E3465DC0'::geometry)"
"Planning Time: 1.707 ms"
"Execution Time: 360.202 ms"
  • If you play with the whole planet data and get results in 350 ms you can't do anything fundamentally wrong. I am pretty sure that you have spatial index on planet_osm_line and that the query is utilizing it. Osm2pgsql seems to create the index as CREATE INDEX ON planet_osm_line USING GIST (way) WITH (FILLFACTOR=100); You can make a comparison by dropping the index but I am not sure what is the exact name that PostgreSQL gives for the index with the above syntax. It is probabl having "planet_osm_line" and "way" in the name. – user30184 Feb 16 at 17:05
  • Hi @user30184. The data I’m experimenting on includes only California. Not the whole planet. Does that change your observation? – Oren_C Feb 16 at 17:07
  • For only California 350 ms might be achieved even without spatial index. Have a look at your database and see if you have the index that I mentioned. It is still a simple and reliable test to drop the index if it exists and re-run your query. A professional would certainly use Explain postgresql.org/docs/12/sql-explain.html . – user30184 Feb 16 at 17:12
  • 1
    the arguments for st_makeEnvelope are in the wrong order, you might be returning much more result than anticipated. Also you may want an index on highway – JGH Feb 16 at 18:48
  • 1
    Default behavior of WITH query has changed since PG12. Maybe try to materialize CTE by adding AS MATERIALZED keyword. Then the most time consumig part of your query (bitmap heap scan) should disapear, or should be much faster. Also try to create partial index on highway field whre you include only required types. – DavidP Feb 18 at 6:40
2

What you really want is to move the LIMIT clause into the pre-fetching, and order the set only for the actual aggregations!

With

  • a GIST on way
  • a plain BTREE on highway (no array => no GIN; an index has likely no effect whatsoever) and
  • VACUUM ANALYZE'd prior to execution

running

SELECT  jsonb_build_object(
            'type',
            'FeatureCollection',
            'features',
            json_agg(ST_AsGeoJSON(q)::jsonb ORDER BY _len)
        )
FROM    (
    SELECT  *, ST_Length(way) AS _len
    FROM    planet_osm_line
    WHERE   highway = ANY('{motorway,trunk,secondary,primary,unclassified,residential,tertiary,track,service}'::text[])
      AND   way @ '0103000020E61000000100000005000000E14830254D515DC013CFFFB8E3465DC0E14830254D515DC0EE5807D4D26C4040FCD56DF3B5674040EE5807D4D26C4040FCD56DF3B567404013CFFFB8E3465DC0E14830254D515DC013CFFFB8E3465DC0'::GEOMETRY
    LIMIT   200
) q
-- WHERE   ST_Within(way, '0103000020E61000000100000005000000E14830254D515DC013CFFFB8E3465DC0E14830254D515DC0EE5807D4D26C4040FCD56DF3B5674040EE5807D4D26C4040FCD56DF3B567404013CFFFB8E3465DC0E14830254D515DC013CFFFB8E3465DC0'::GEOMETRY)
;

yields results in a constant <80ms, on a local mid-tech setup, and PG10(no proper worker support yet, so you may end up with a better performance)/PostGIS3.

Updated...

...to run a (preliminary) bbox check in the sub-query; this enforces the planner to consider an Index scan instead of a Bitmap Index Scan, decreasing fetching by another 15ms on my machine:

Aggregate  (cost=1415.89..1415.90 rows=1 width=32) (actual time=41.161..41.162 rows=1 loops=1)
  ->  Subquery Scan on q  (cost=0.41..1415.87 rows=1 width=1417) (actual time=0.465..3.136 rows=200 loops=1)
        ->  Limit  (cost=0.41..1408.37 rows=200 width=1127) (actual time=0.396..1.114 rows=200 loops=1)
              ->  Index Scan using planet_osm_line_way_idx on planet_osm_line  (cost=0.41..12207.41 rows=1734 width=1127) (actual time=0.395..1.022 rows=200 loops=1)
                    Index Cond: (way @ '0103000020E61000000100000005000000E14830254D515DC013CFFFB8E3465DC0E14830254D515DC0EE5807D4D26C4040FCD56DF3B5674040EE5807D4D26C4040FCD56DF3B567404013CFFFB8E3465DC0E14830254D515DC013CFFFB8E3465DC0'::geometry)
                    Filter: (highway = ANY ('{motorway,trunk,secondary,primary,unclassified,residential,tertiary,track,service}'::text[]))
                    Rows Removed by Filter: 62
Planning time: 0.620 ms
Execution time: 41.330 ms

Notes:

  • if you are consistently looking for bbox containment, you don't need to check for actual ST_Within; the @ bbox operator has the same effect in this case
  • with the default behavior of CTEs changed, a WITH clause can now get optimized as if it were a sub-query, which is quite desirable in this case (meaning that the execution plan should be equal to the above)
  • having the length computed on the actual selection (LIMIT 200) only and used as order expression in the json_agg, you save quadrillions of computations
  • if you happen to not being interested in any other columns, don't fetch them
  • make sure you don't use arbitrary bboxes; your given envelope seems to cover most of the world except California...
  • A partial index on the given highway values has no (positive) effect; in fact, the search values are about 20% of distinct values in highway, the planner doesn't even try to look up any index
  • ǸULL value order (NULLS FIRST/LAST) doesn't make much sense since there is no ordering based on that column.
  • An incredibly detailed and helpful answer. Thank you! – Oren_C Feb 18 at 11:49
  • @Oren_C you're welcome! I just updated the query to include a bbox check in the subquery; that gave me another 15ms decrease. Note that this is not the end of it; some of the comments to your question have suggestions I haven't covered (and tested), so you may want to get your fingers dirty ;-) – geozelot Feb 18 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.