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.

• Ian Turton: I think once you do a transform the index is useless
• Vince: The transformed coordinates are not indexed, so a full table scan is required. Try adding a constraint that deprojects your bounding box or adding a covering index on the transformed data.
• geozelot: also, don't transform, you colum is in SRID:3857
• Vince: Doh! Yeah, this ^^^^^ too.
• user30184: Isn't that box rather large BOX3D(-3.402823466385289e+38 -3.402823466385289e+38,3.402823466385289e+38 3.402823466385289e+38)?

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 !