Use ST_difference and ST_intersection with Multipolygons in PostGIS

Question

I'm trying to use ST_difference and ST_intersection with two layers of multipolygons.

I want something like:

With the input on the left and the result on the right.

MY DATA:

t1.geom contains 1000 differents polygons.

t2.geom contains 1000 differents polygons.

THE QUERIES:

I try to create (in a naive way) two queries to perform these operations

st_intersection(t1.geom, t2.geom)
from t1,t2
where st_intersects(t1.geom,t2.geom);

Unfortunately it doesn't work, or takes forever to run.

I would like a query similar to the tools erase and intersect from ArcGIS.

QUESTION:

How to perform those operations efficiently ?

Answer 1

I finally found an answer by myself.

For st_difference():

with temp as 
(
  select   b.gid, st_union(a.geom) as geom
  from     t1 b join t2 a on st_intersects(a.geom, b.geom)
  group by b.gid
) 
select st_difference(b.geom,coalesce(t.geom, 'GEOMETRYCOLLECTION EMPTY'::geometry)) as newgeom
from t1 b left join temp t on b.gid = t.gid

It works like a charm and runs fast. The query is similar for st_intersection().

In addition, some of my polygons weren't valid so I corrected the geometry with

update t1
set geom = ST_Multi(ST_CollectionExtract(ST_MakeValid(geom), 3));
update t2
set geom = ST_Multi(ST_CollectionExtract(ST_MakeValid(geom), 3));