PostgreSQL query slows due to ORDER BY even with an index I have the following query to find offices that service a given area. When I run this query without the "ORDER BY" clause, it takes a less than 0.1 seconds. When I add the ORDER BY clause, it takes about 2 seconds on our data set. 

SELECT off.provider_id, st_AsText(off.area), st_area(off.area)
FROM offices off
WHERE st_contains(off.area, :location)
ORDER BY st_area(off.area)
LIMIT 10 OFFSET :offset;


When this first happened I realized, "Duh! there's no index"; ST_area is computing the area covered by a geospatial region and since I'm ordering on it, the optimizer must be checking all containment-filtered results at query time.

CREATE INDEX offices_st_area_idx
ON public.offices
USING btree
(st_area(area));


This addition had no effect on query time. Is there a faulty assumption I'm making here? Anybody have an idea of how I can speed this query up? I'd like to render this view without having to resort to a 'loading' page.
If you don't have an ORDER BY clause the database takes any 10 records that meet your criteria and returns them. With ORDER BY it first finds all rows that meet criteria and then orders them.

You could try enabling btree-gist and then creating index on the spatial column and the area

CREATE INDEX offices_st_area_idx 
ON public.offices
USING gist (area, st_area(area));


But otherwise then that it's a normal question regarding performance and depends heavily on your data and server settings.