On Thu, Aug 21, 2014 at 4:29 PM, Eli Naeher <enaeher@xxxxxxxxx> wrote: > Clearly the bulk of the time is spent sorting the rows in the original > table, and then again sorting the results of the subselect. But I'm afraid I > don't really know what to do with this information. Is there any way I can > speed this up? "Sort Method: external merge Disk: 120976kB" The obvious first step is to bump up work_mem to avoid disk-based sort. Try setting it to something like 256MB in your session and see how it performs then. This may also allow the planner to choose HashAggregate instead of sort. It not always straightforward how to tune correctly. It depends on your hardware, concurrency and query complexity, here's some advice: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#work_mem_maintainance_work_mem Also you could create an index on (route, direction, stop, stop_time) to avoid the inner sort entirely. And it seems that you can move the "INNER JOIN stop" to the outer query as well, not sure if that will change much. Try these and if it's still problematic, report back with a new EXPLAIN ANALYZE Regards, Marti -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
