How can I optimize this subquery as join?

Question

I've noticed that running this subquery

SELECT ST_Area(ST_Union(ST_Transform(ST_Intersection((SELECT poly1.the_geom from poly1 WHERE poly1.polygon_type='P'),poly2.the_geom),3857)))

AS area_of_P FROM poly1, poly2

is significantly slower than running this join

SELECT ST_AREA(ST_Union(ST_Transform(ST_Intersection(poly1.the_geom,poly2.the_geom),3857)))

AS area_of_poly

FROM poly2

LEFT JOIN poly1 on st_intersects(poly1.the_geom,poly2.the_geom)

WHERE poly2.polygon_type='P'

However, I need to expand upon this second joined version to return more columns, each with the area of a given polygon type calculated, i.e.

SELECT ST_Area(ST_Union(ST_Transform(ST_Intersection((SELECT poly1.the_geom from poly1 WHERE poly1.polygon_type='P'),poly2.the_geom),3857))) AS area_of_P,

ST_Area(ST_Union(ST_Transform(ST_Intersection((SELECT poly1.the_geom from poly1 WHERE poly1.polygon_type='S'),poly2.the_geom),3857))) AS area_of_S

FROM poly1, poly2

Answer 1

Try this.

SELECT ST_AREA(ST_Union(ST_Transform(ST_Intersection(poly1.the_geom,poly2.the_geom),3857)))

AS area_of_poly

FROM poly2

LEFT JOIN poly1 on st_intersects(poly1.the_geom,poly2.the_geom)

WHERE poly2.polygon_type IN ( 'P', 'S' )

Edit:

SELECT ST_AREA(ST_Union(ST_Transform(ST_Intersection(ps.the_geom,poly2.the_geom),3857))) AS area_of_P,
       ST_AREA(ST_Union(ST_Transform(ST_Intersection(ss.the_geom,poly2.the_geom),3857))) AS area_of_S
FROM poly2
JOIN poly1 ps ON poly2.polygon_type = 'P' AND st_intersects(ps.the_geom,poly2.the_geom)
JOIN poly1 ss ON poly2.polygon_type = 'S' AND st_intersects(ss.the_geom,poly2.the_geom)