Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
    
Some more info on CLUSTER. –  radek Dec 4 '11 at 0:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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