Skip to content

Performance issue when migrating to 0.8.1 version #909

@AlexGacon

Description

@AlexGacon

Hi there,

We are trying to migrate from version 0.7.1 to 0.8.1 and we have a quite important increase of the duration of the requests to baremaps on the same environments (less than 1 second in 0.7.1 and more than 10 seconds in 0.8.1).

When I look at the generated PG requests, I wonder if the where clause is at the right place.

For example in:

SELECT 
  ST_ASMVT (MVTGEOM.*, 'ban_numero') 
FROM 
  (
    SELECT 
      ST_ASMVTGEOM (
        T.GEOM, 
        ST_TILEENVELOPE (18, 132979, 90364)
      ) AS GEOM, 
      T.TAGS - 'id' AS TAGS, 
      T.ID AS ID 
    FROM 
      (
        SELECT 
          ROW_NUMBER() OVER (
            ORDER BY 
              ADDOK_ID DESC
          ):: INTEGER AS ID, 
          JSONB_BUILD_OBJECT(
            'id', ADDOK_ID :: TEXT, 'numero', NUMERO :: TEXT
          ) AS TAGS, 
          GEOM 
        FROM 
          IGN.BAN_NUMERO
      ) AS T 
    WHERE 
      T.GEOM IS NOT NULL 
      AND T.GEOM && ST_TILEENVELOPE (
        18, 
        132979, 
        90364, 
        MARGIN => (64.0 / 4096)
      )
  ) AS MVTGEOM

if I change the where clause position like this:

SELECT 
  ST_ASMVT (MVTGEOM.*, 'ban_numero') 
FROM 
  (
    SELECT 
      ST_ASMVTGEOM (
        T.GEOM, 
        ST_TILEENVELOPE (18, 132979, 90364)
      ) AS GEOM, 
      T.TAGS - 'id' AS TAGS, 
      T.ID AS ID 
    FROM 
      (
        SELECT 
          ROW_NUMBER() OVER (
            ORDER BY 
              ADDOK_ID DESC
          ):: INTEGER AS ID, 
          JSONB_BUILD_OBJECT(
            'id', ADDOK_ID :: TEXT, 'numero', NUMERO :: TEXT
          ) AS TAGS, 
          GEOM 
        FROM 
          IGN.BAN_NUMERO AS TOTO 
        WHERE 
          TOTO.GEOM IS NOT NULL 
          AND TOTO.GEOM && ST_TILEENVELOPE (
            18, 
            132979, 
            90364, 
            MARGIN => (64.0 / 4096)
          )
      ) AS T
  ) AS MVTGEOM

I get the previous durations.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions