1

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?

EDIT:

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
  • What is the output of EXPLAIN (ANALYZE, BUFFERS) – JGH Mar 6 at 21:10
  • 2
    Suggest Mapbox to add postgis.net/docs/ST_EstimatedExtent.html as an alternative. User must keep the statistics up to date. – user30184 Mar 6 at 21:51
  • @jgh see my edited question for the explain (analyze, buffers) – Georg Mar 6 at 21:58
  • 2
    ST_Extent does not use the geometry index. It evaluates the full geometries, thus it takes a while as it scans thru each geometry in the table. – Mike T Mar 6 at 23:12
3

ST_Extent is slow because it "does not read the serialized cached bounding box (since that is floating point) but calculates the box in full from the underlying geometry." (from postgis/lwgeom_box3d.c).

If you need an approximate extent that is 95% close, use ST_EstimatedExtent.

If the table is brand-new or has been significantly altered, update the statistics:

VACUUM ANALYZE cont;

Then get an approximate box2d result:

SELECT ST_EstimatedExtent('cont', 'geom');
  • Wow... ST_EstimatedExtent is blazing fast... I worked around the problem by using a command-line tool that does not ask for the extent... but that would for sure improve the export speed a lot... but as Mapbox Studio Classic is no longer maintained, I guess they won't change it... Anyway... thanks a lot!!! – Georg Mar 7 at 9:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.