Create an efficient index for tile creation

[bloussou]

Description and goal

Spotted by @multun :

Currently django is creating automatically a spatial index. We should try spatial_index=False when creating geographic fields in django and add manually a composite gist on (infra_id, geographic)

Acceptance criteria

Request should be faster:

• 2.56s

  http://localhost:8090/layers/tile/track_sections/geo/7/64/44/?infra=1

• 29s

  http://localhost:8090/layers/tile/track_sections/geo/5/16/11/?infra=1

• 2.5s

  WITH bbox AS (
   SELECT TileBBox(5, 16, 11, 3857) AS geom
  )
  SELECT ST_AsGeoJson(geographic) AS geom,
   track_section.data AS data
  FROM osrd_infra_tracksectionlayer layer
   CROSS JOIN bbox
   inner join osrd_infra_tracksectionmodel track_section on track_section.obj_id = layer.obj_id
   and track_section.infra_id = layer.infra_id
  WHERE layer.infra_id = 1
   AND geographic && bbox.geom
   AND ST_GeometryType(geographic) != 'ST_GeometryCollection'

[multun]

I ran some tests and figure out that postgres indeed uses indexes when the bbox is small enough. it is smart enough to figure out that it needs all the data anyway to serialize everything

[multun]

For example, this works fine:

 WITH bbox AS (
     SELECT TileBBox(15, 16562, 11421, 3857) AS geom
 )
 SELECT ST_AsGeoJson(geographic) AS geom,
     track_section.data AS data
 FROM osrd_infra_tracksectionlayer layer
     CROSS JOIN bbox
     inner join osrd_infra_tracksectionmodel track_section on track_section.obj_id = layer.obj_id
     and track_section.infra_id = layer.infra_id
 WHERE layer.infra_id = 1
     AND geographic && bbox.geom
     AND ST_GeometryType(geographic) != 'ST_GeometryCollection'