[postgis-users] Compound Index? (Integer + Spatial)

[Yurgis Baykshtis]

Let me describe my problem using some fake sample for the simplicity
sake:

1. There is a database with two tables: maps and cities.
2. There are many maps and many cities in each map.
3. Each map has an integer map_id as a primary key
4. Each city has 2D location as POINT (x, y) and map_id it belongs to
5. The task is to find cities on a given map inside a given box:

    SELECT FROM cities WHERE map_id = my_map_id AND location && '(x1,
y1, x2, y2)'::box

I am quite confused on how to build a proper index to make this query:

In 1-D world this would be a compound on map_id and location.
However, in PostreSQL and PostGIS I don't seem to be able to that.

So, when I use a spatial index on location, the query above will scan
through all maps and then use map_id = my_map_id as filter.

I can think of a three possible solutions:

1. Store each map in a separate DB 

2. Store each map in a separate table.

3. Create a FAKE spatial column something like POINT (map_id, map_id)
and then build a compound index on this column and location:

    SELECT FROM cities WHERE map_id && '(my_map_id, my_map_id)::box
           AND location && '(x1, y1, x2, y2)'::box

But none of the above looks graceful.

Any idea on how to do this RIGHT?