GIST index not used by query optimizer on geometry field

Question

I'm trying to add elevation curves on an OpenStreetMap tile server I'm currently building. I created a table in PostgreSQL to store the shapes of the whole world. Structure was defined by gdal_contour tool as follows :

                                                              Table "public.contour100"
    Column    |           Type            | Collation | Nullable |                   Default                   | Storage | Stats target | Description 
--------------+---------------------------+-----------+----------+---------------------------------------------+---------+--------------+-------------
 ogc_fid      | integer                   |           | not null | nextval('contour100_ogc_fid_seq'::regclass) | plain   |              | 
 id           | numeric(8,0)              |           |          |                                             | main    |              | 
 height       | numeric(12,3)             |           |          |                                             | main    |              | 
 wkb_geometry | geometry(LineString,3857) |           |          |                                             | main    |              | 
Indexes:
    "contour100_pkey" PRIMARY KEY, btree (ogc_fid)
    "contour100_wkb_geometry_geom_idx" gist (wkb_geometry)

This table contains 45 341 726 rows.

My CartoCSS YAML project references this table like so :

- id: contour100
    geometry: linestring
    Datasource:
      type: "postgis"
      dbname: "myDatabase"
      key_field: ""
      geometry_field: "wkb_geometry"
      extent: "-20037508,-20037508,20037508,20037508"
      table: (SELECT
            ST_Transform(wkb_geometry, 3857) AS wkb_geometry, height
            FROM contour100
            WHERE wkb_geometry && !bbox!
          ) AS c100

When rendering tiles, Mapnik generates queries that look like this one :

SELECT ST_Transform(wkb_geometry, 3857) AS wkb_geometry, height
FROM contour100
WHERE wkb_geometry && ST_SetSRID('BOX3D(-3.402823466385289e+38 -3.402823466385289e+38,3.402823466385289e+38 3.402823466385289e+38)'::box3d, 3857)

This works and renders my elevation curves nicely, but takes several minutes to process the data.

Using EXPLAIN, PostgreSQL outputs this :

Seq Scan on contour100  (cost=0.00..4827531.68 rows=44987512 width=37)
   Filter: (wkb_geometry && '01030000A011[...]'::geometry)

As you can see, I have a spatial GIST index on my wkb_geometry column, but the query optimizer chooses a sequential scan. Being unfamiliar with geography indexing, I don't understand the reason why a sequential scan is performed.

I've run VACUUM ANALYSE on this table, and tried CLUSTER on the wkb_geometry column as well, but the GIST index was never used.

Answer 1

In the end I was just missing the minzoom property in my layer definition. It was defined in the stylesheets only, so the query was executed on every zoom level regardless of it being used for the meta-tile generation.

My layer now looks like so :

- id: contour100
    geometry: linestring
    Datasource:
      type: "postgis"
      dbname: "myDatabase"
      key_field: ""
      geometry_field: "wkb_geometry"
      extent: "-20037508,-20037508,20037508,20037508"
      table: (SELECT
            wkb_geometry, height
            FROM contour100
            WHERE wkb_geometry && !bbox!
          ) AS c100
    properties:
      minzoom: 12

Thanks for pointing me to the right direction !