PostGIS: Is it possible to identify overlapping polygons in the same layer using their geomteries?

Question

I have a single table containing the geometries of ~3 million polygons and I am looking to calculate the number of polygons that overlap with another.

I know that you are able to do this in QGIS using the Topology Checker Plugin in Plugin Manager, I have used this when comparing much smaller numbers of polygons however I don't see how this would handle such a large amount of polygons. Also, from my research you are unable to use the results i.e. get a list of id's/display a map

After deciding the Topology Checker Plugin wouldnt work I found this:

http://postgis.17.x6.nabble.com/Method-to-remove-overlaps-in-a-layer-td4621793.html

I followed the steps and created the topology for all of the polygons successfully however my relation table wasn't populated and therefore the final step didn't work:

SELECT r.element_id as face, array_agg(r.topogeo_id) as geoms 
FROM topo3.relation r 
WHERE r.element_type = 3 -- 3 == face 
GROUP BY face 
HAVING count(r.topogeo_id) > 1; 

I am running out of ideas and was wondering if anyone could possibly help

Answer 1

select *
  from b.mypolygons as t1, b.mypolygons as t2
  where ST_Overlaps(t1.geom, t2.geom) = true
    and t1.my_id < t2.my_id
;

Assuming my_id is integer or other type where '<' makes sense. If not use '<>', but the you'll get 2 records out for each overlap.