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.
BOX3D(-3.402823466385289e+38 -3.402823466385289e+38,3.402823466385289e+38 3.402823466385289e+38)
?