Optimizing PostGIS query on table using buffered point

Question

I have a global dataset of ecoregion boundaires. There are ~809 unique polygons in this dataset. I've imported it into PostGIS with SRS of 4326 and built a spatial index using the function below

CREATE INDEX ecoregionsindex ON ecoregions USING gist(geom);

Querying with a point takes ~150ms to run

SELECT id FROM "ecoregions"
WHERE ST_Within (ST_PointFromText('POINT(-111 45)', 4326), geom);

Buffering ~20,000m around this point using the ST_DWithin() method takes about 15,000ms to run.

SELECT id
FROM "ecoregions"
WHERE ST_DWithin(ecoregions.geom, ST_MakePoint(-111,45)::geography, 
20000);

Is there a way to optimize my data or the query above to run more quickly?

@jotamon well it looks like you've found a solution to cast your geometry as geography in your spatial index.... I'm guessing the data may cover the entire globe? If so, you might want to store your data as geography rather than geometry so you don't have to cast your data in the index (is that OK to do that even though you can - I don't know) — DPSSpatial_BoycottingGISSE, Nov 18 '19 at 18:11

score 5 · Accepted Answer · answered Nov 18 '19 at 16:36

You need to construct your index the way you are gonna use it. If you cast on your call, you need to cast the same way on your index (here you need to create your index on the geography version of your point):

CREATE INDEX ecoregionsindex_geog ON ecoregions USING gist(CAST(geom AS geography));

Note that the :: shortcut don't work for indexes. Don't forget to analyze after:

ANALYZE ecoregions;

Optimizing PostGIS query on table using buffered point

1 Answers1