I have a set of geometry in my PostgreSQL table that consists of Points, Linestrings & Polygons. From this set I need to create a subset of geometries which intersects with each other. I need a method without looping through each one of them as there can be more than 1000 geometries at once.
I need to fetch the geometries that are marked in red in this image
Create a spatial index for your table and make a self join with SQL:
SELECT * FROM my_table a INNER JOIN my_table b ON a.geom && b.geom
WHERE ST_Intersects(a.geom, b.geom) and ST_AsText(a.geom) <> ST_AsText(b.geom);
&& is the operator for the overlaping of the minimal boundary rectangles (MBR) and it uses spatial index. For 1000 geometry it should be fast enough, if few overlapings are among the MBR of the geometries (your figure shows that).
&& is not necessary since ST_Intersects automatically includes an index filter.
a.geom for each b.geom that it intersects with; in OPs example set above, the middle polygon of those three-in-a-row will be present twice. There are better and faster solutions for this kind of task than needing to use a DISTINCT on a large and heavily computed result set with unnecessarily duplicated information - mainly using an EXISTS filter, or ST_ClusterDBSCAN with eps:=0.