search

transitland / transitland-datastore

Transitland v1 core components. Deprecated and only maintained occasionally. See Transitland v2.
https://transit.land/documentation/datastore/
MIT License
105 stars 18 forks source link

Slow SSP pagination #610

Closed irees closed 8 years ago

irees commented 8 years ago

The ordering on most SSP queries can be very slow.

irees commented 8 years ago

Use of a CTE query for sorting (https://www.postgresql.org/docs/current/static/queries-with.html) can improve performance substantially -- tested with a local dump of prod db. The postgres_ext (https://github.com/DockYard/postgres_ext/blob/master/docs/querying.md) gem adds this support, but it appears to have a conflict with squeel. Investigating.

drewda commented 8 years ago

Perhaps of use: CTE support in ARel https://github.com/rails/arel#complex-joins ?

drewda commented 8 years ago

This is the query that was taking longer than 2 minutes to complete previously: https://transit.land/api/v1/schedule_stop_pairs?feed_onestop_id=f-drt8-nps~boha~ferries

After @irees 's index changes, it's nice and fast.