Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Here's a simplified version of the queries I am running:

    join edition_events on edition_events.edition_id =
    join events on = edition_events.event_id
    join (
        group by
        ) as dates on dates.event_id =
WHERE = ?;

These queries ran great with my test data (~1500 events). Now that I'm starting to use a copy of the real data (~400,000 events, ~500,000 event_dates), my application is timing out before I get results back from the database. I have added indexes on all relevant columns, which only helped a little bit. I've narrowed down the problem to the subqueries that use aggregation.

Now the real version of these subqueries does things like time zone manipulation or a count instead of array_agg, so using a trigger to update a counter column is not practical in many instances. The dates associated with events must be individual rows rather than stored as an array since they have a foreign key relationship with other tables.

What would be my best bet for improving performance here? I understand that the subquery is being evaluated first, but I don't know which dates I actually need to look at until I've done the joins. I have looked at lateral joins, but I am uncertain if they would help here (and most information I can find is along the lines of "look, new feature!"). Since I am still using 9.2, I don't want to ask to upgrade to 9.3+ if it won't actually solve my problem.

share|improve this question
up vote 2 down vote accepted

Your approach ("aggregate first, join later") generally performs best when you need to retrieve the whole table or large parts of it - or as long as the aggregated table is small.

For only a few rows in the result, this gets disproportionately expensive for big tables, since the whole table (event_dates in your case) has to be processed. In these cases, it's better to revert the paradigm to "join first, aggregate later". The equivalent query would then be:

      ,array_agg(ed.date_start) AS starts
      ,array_agg(ed.date_end)   AS ends
FROM   editions       e
JOIN   edition_events ee ON ee.edition_id =
JOIN   events         ev ON = ee.event_id
JOIN   event_dates    ed ON ed.event_id =

This should help with any version of Postgres.
Also using table aliases to simplify the syntax.

I don't see how LATERAL (Postgres 9.3+) could help this query. You would use it to reference a column from the left side of the join tree that you couldn't access on the same query level otherwise, often to produce multiple rows to join to. Neither is the case here. What you are thinking of can be implemented with plain old correlated subqueries, but those typically perform worse.
Don't take my word for it. Run a quick test with EXPLAIN ANALYZE for your case (with your real life data), then you know for sure.

share|improve this answer
Would the availability of lateral joins change anything? Or is this still the most efficient way no matter what? – cimmanon Dec 9 '13 at 17:14
@cimmanon: I added a paragraph for that. – Erwin Brandstetter Dec 9 '13 at 17:43

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.