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?

1 Answer 1


Indexing a column that is used in either joins or where clauses is the best (and simplest) way to make a query efficient, so the answer is yes.

Another trick you could pull is to CLUSTER the table against the index (remember to run VACUUM ANALYZE after that to update table statistics). CLUSTER makes a copy of the table (the table will not be usable during clustering) reordering the data so that identical values of the index are contiguously stored on disk (thus maximing disk read-ahead efficiency).

Make sure to read the documentation on the CLUSTER command to understand if you need it and how it works.

Please note though that Postgres will not use the index when the condition is IS NULL.


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.