Since I had a few minutes I´d like to add up to this with a few words about the core concept of index aware queries (SARGable, for Search ARGument -able; make use of any index on search columns):
Indexes on search columns is a fundamental element in database design and for the complex geometric relation analysis in PostGIS in particular. Having a (spatial) index in place and utilize it properly is what makes a query run in 2 ms instead of 5 min (not as a rule...you get the point).
In PostGIS, the primarily used
GIST index for geometries essentially creates a list of bounding boxes for each geometry; when the respective table is then searched for spatial relations (e.g.
ST_Contains), the planner can instead look up the index list and compare bounding boxes (thus geometries consisting of only 4 (or, really, 5) points) instead of tens, hundreds or thousands of vertices that make up the real geometry.
This being said, your query structure, however perfectly valid (assuming the corrections posted as the above answer), denies the planner to utilize the index in the search; the main problem is
ST_Union effectively creates a new (more complex) geometry that can not be indexed on the fly. The operation itself is costly. And for every search operation (spatial relation) it will now need to traverse every vertex in the resulting geometry. This is also a reason why, in general, having Multi geometries is bad practise performancewise; you can index them alright, but in the final comparison, the number of distinct geometries and their number of vertices is unproportionally larger than for Simple geometries...somewhat defeating the purpose of an index.
I´m still not quite sure if an
UNION ALL select over two tables will actually keep the result fully SARGable, but the smaller geometries at least will make a difference. You won´t necessarily notice that on the small scale of your search, but with just a few polygons more you can see the execution time rise (exponentially, even).
The following structure is only one of many possible, but should be reasonably close to yours to be comprehensive and benefits from what I said above. It avoids the need of a single MultiPolygon by comparing the set of rows created in the
FROM block (and simulates the
true/false by either printing
true if a match is found or simply returning no row if no match was found):
SELECT true AS is_within -- you can select all columns mentioned in the subquery here, e.g. `id`...
SELECT geom -- ...but no need to reference the `id` column if you don´t need it
WHERE id IN (1, 10, 12)
SELECT geom -- (same here)
WHERE id IN (14, 15, 13)
) AS polys
ST_MakePoint(3, 46) -- if possible, use this, as it is faster and more precise