Modeling data in PostGIS when Several Join queries with Geometry are Needed

Question

Suppose you have a database where you have several tables with geometric information and you have several cases in your system where you need to query the tables with a lot of joins (ie: LEFT JOIN table2 ON st_intersects(table1.the_geom,table2.the_geom)).

I believe this type of query is slow compared to a "normal" int field join, even if you use spatial indexes on the fields.

In a system where this situation arrives a lot, how do you model the data? For example, I have many situations where you would have to query this, joining with 10 other tables. Each table holds a different type of information, but I need to see if a row in table1 intersects with the geometry of all the other tables.

I thought about creating a many-to-many relationship, relating the ids to make the queries run faster. But then I would need a trigger to each time a new row is inserted, check which ones it intersects on each possible relationship (10, in the example) and save it. This would make the inserts a lot slower too.

Is there a common pattern to solve this type of problem on PostGIS?

Answer 1

If you require all joins to return true, then change your LEFT JOIN to an INNER JOIN. INNER JOIN tends to be optimized better for spatial than LEFT is. Also since a INNER JOIN requires matches it can often skip over records where the first two joins don't match.

You might also want to play around with join_collapse setting in PostgreSQL. Like

SET join_collapse_limit='2';

Then run your query. I've seen this make a big difference with queries that have many joins.

The other option is to use WITH clauses. When using CTEs you want to be a bit careful. For this I join tables in a CTE expression that will return the least number of records and then use this for my later joins. It materializes and forces postgres to process your table expressions first in order you designate so you can better control the order of joining. You will lose index usuage in final query so yo want to be a bit careful.