0

I am fetching MVT tiles from a PostGIS database in combination with pg_tileserve. When fetching tiles from a fairly small dataset (some 10000 features) this works quite performant. But when fetching features from a huge database like 10000000+ features (e.g. Linestrings) this is getting very slow although when viewing the map on a higher (18) zoomlevel. When I guess the retrieved amount of data is the same.

This seems somehow unexpected to me, as I would expect PostGIS to efficiently retrieve features as it is a geospatial database.

Can someone tell me if there are any recommendations for configuring pg_tileserve with PostGIS to improve performance. Or is this in the end a bad combination?

Calls towards PostGIS:

SELECT ST_AsMVT(mvtgeom, 'public.roadlinesmy_table', 4096, 'geometry') FROM (
        SELECT ST_AsMVTGeom(
                ST_Transform(ST_Force2D(t."geometry"), 3857),
                bounds.geom_clip,
                4096,
                256
          ) AS "geometry"

          , "PRPASSAGE", "HOUSE_N", "Dest", "A", "S", "TUNNEL_NAME", "ROAD_NUMBER", "ROAD_NAME", "P", "I", "F", "m", "id", "HR", "G", "RO", "SPEED", "B", "g", "R", "road", "ADMIN", "FUNCT", "WARN", "NUM"

        FROM "public"."roadlinesmy_table" t, (
                SELECT ST_MakeEnvelope(1.0273136601527669e+06, 6.2421534778806195e+06, 1.0370975997732691e+06, 6.251937417501122e+06, 3857)  AS geom_clip,
                                ST_MakeEnvelope(1.0267021639264855e+06, 6.241541981654339e+06, 1.0377090959995505e+06, 6.252548913727403e+06, 3857) AS geom_query
                ) bounds
        WHERE ST_Intersects(t."geometry",
                                                ST_Transform(bounds.geom_query, 3857))

        LIMIT 50000
) mvtgeom
8
  • 2
    It will always take time to read and process 10000000+ features - do you have indexes on your table. Please provide the explain results for the query
    – Ian Turton
    Aug 2, 2023 at 9:46
  • what are explain results? do I have to configure postgis to use indexes? Aug 2, 2023 at 9:47
  • 2
    explain is a SQL command that you prepend to your query to explain where the time is going. And yes of course you need to create indexes it is not magic
    – Ian Turton
    Aug 2, 2023 at 10:28
  • 1
    Low zoom levels are the slowest of all. At zoom level 0 one tile covers the whole world, so every feature from the database. Filter with SQL what data are relevant to include into the low zoom levels, and index the fields that are needed for filtering. I see that you do have LIMIT 50000, but using limit without indexes is sometimes slow.
    – user30184
    Aug 2, 2023 at 12:08
  • I now created an index via: "CREATE INDEX spatial_index ON "+dbTableName+" USING GIST ( geometry );"; and afterwards VACUUM ANALYZE; is this correct or are there more optimizations available? Aug 2, 2023 at 15:30

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.