Speed up slow Postgres query with many joins

Question

My site has a query that joins several tables, some with many thousands of rows. When the cache misses, this can leave users waiting for 10+ second queries, sometimes over 20 seconds.

The largest tables involved are event_occurrences and bookings, with approximately 50k rows each. You can view the full query + plan here: https://explain.dalibo.com/plan/1BE.

I added an additional index recently (to event_occurrences.start_time), but the query is still very slow. Perhaps I should add a composite index with id, start_time, and blackout instead? Any other suggestions to speed this query up without a total schema redesign?

Answer 1

It looks like all (99%) of the time is spent doing the sort just before the unique. The unique brings the data set down from 13K rows to 300 so potentially there is room to reduce the data set going into that sort.

Things like using semi-joins or adding join filters might be the way to go so you don’t introduce duplicate results. The join to photos looks to contribute a lot. You could skip the join then load photos separately. - user217655