I am trying to make my own vector tiles and I am using Mapbox Studio Classic for that purpose. When I export my dataset, Mapbox Studio will try to figure out the extend of my data and those queries take very long.
The query it's performing is like this:
SELECT ST_XMin(ext), ST_YMin(ext), ST_XMax(ext), ST_YMax(ext) FROM (SELECT ST_Extent(geom) as ext from cont) as tmp`
And my cont table is quite huge with a lot of short (non of them is long) contour lines in it. It has an index on the geom column (GIST).
The query is taking a LOT of time (several minutes) on our beefed up machine.
Is there a way to improve the performance of that query?
EXPLAIN (ANALYZE, BUFFERS) generates:
Subquery Scan on tmp (cost=13016236.00..13016236.04 rows=1 width=65) (actual time=2245959.393..2245959.396 rows=1 loops=1) Buffers: shared hit=39596394 read=21929928 -> Aggregate (cost=13016236.00..13016236.01 rows=1 width=5190) (actual time=2245952.748..2245952.748 rows=1 loops=1) Buffers: shared hit=39596394 read=21929928 -> Seq Scan on cont (cost=0.00..12830974.00 rows=74104800 width=5190) (actual time=0.014..104310.564 rows=74119236 loops=1) Buffers: shared hit=3343020 read=8746906 Planning time: 0.086 ms Execution time: 2245959.495 ms