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?