I have two PostGIS tables: one of
streets, and one of
regions, which, apart from spanning the same spatial extent, are a priori unrelated. The
streets queries I perform can be restricted to a given region, by supplying a known id. To exploit this, I added a
region_id column to the
streets table (using a
st_intersects-based update query, which was quite expensive, but performed only once).
My question: once this is in place, will the use of an index on the
streets.region_id column be enough to make queries of this type maximally efficient:
select * from streets where region_id = <id> and ...
Or is there an alternative mechanism that would perform better?