I've 2 sets of polygons in 2 tables and the sets overlap each other. For each polygon in set A, I get the ID of the polygon in set B that it overlaps the most.
This is the query:
SELECT DISTINCT ON (a.id)
a.id as a_id,
b.id as b_id,
ST_Area(ST_Intersection(a.geom, b.geom)) as intersect_area
FROM a, b
ORDER BY a.id, ST_Area(ST_Intersection(a.geom, b.geom)) DESC
It works properly so, what's the problem? It's very slow (it takes several minutes). Table "a" has more than 10,000 rows and table "b" 700 rows. So, how can I improve the perfomance? I've proved with spatial index but I haven't had better results.

Formed in 2009, the Archive Team (not to be confused with the archive.org Archive-It Team) is a rogue archivist collective dedicated to saving copies of rapidly dying or deleted websites for the sake of history and digital heritage. The group is 100% composed of volunteers and interested parties, and has expanded into a large amount of related projects for saving online and digital history.
