Optimizing PostGIS query

Question

I've a query that has to be run against 1 million records. The query finds the intersection between two polygons and the area of the result of the intersection. Here's the query:

SELECT 
 ST_Area(
 ST_Collect(
 ST_Intersection(poly1.the_geog::geometry, poly2.the_geog::geometry)
 )::geography
 ),
 ST_AsGeoJSON(
 ST_Multi(
 ST_Collect(
 ST_Intersection(poly1.the_geog::geometry, poly2.the_geog::geometry)
 )
 )
 ) 
FROM poly1, poly2 
WHERE poly1.id = 1 AND
 ST_Intersects(poly1.the_geog::geometry,poly2.the_geog::geometry)

I'm trying to figure out if I can use the result of ST_Intersection query which is common for ST_AsGeoJSON and for calculating the area using ST_Area. Is it possible to do it ?

Answer 1

Use nested SELECT statement. In the inner SELECT run the ST_Intersection, then pass the result to the outer SELECT to ST_Area and ST_AsGeoJSON. Be sure to have indices built on your table as well.