Re: Window functions, partitioning, and sorting performance

On Thu, Aug 21, 2014 at 4:29 PM, Eli Naeher <enaeher(at)gmail(dot)com> 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