Return to Answer

You could try simplifying and using LATERAL for joining the laps table:

SELECT 
  races.*, tmptimers.last_start_time, tmplaps. last_updated_at
FROM 
  races
LEFT JOIN 
  timers AS tmptimers 
    ON  tmptimers.user_id = 1 
    AND tmptimers.race_id = races.id
LEFT JOIN LATERAL
  ( SELECT 
      updated_at AS last_updated_at
    FROM 
      laps 
    WHERE 
      user_id = 148
      AND updated_at > '2014-06-13'
      AND race_id = races.id
    ORDER BY 
      updated_at DESC
    LIMIT 1
 ) AS tmplaps
     ON TRUE  
WHERE
  races.account_id = 5
  AND races.id IN (29, 30, ..., 74)
ORDER BY
  last_updated_at DESC ;

I'm not sure what index will be best for the laps table though. Perhaps an index on (user_id, race_id, updated_at).

You could try simplifying and using LATERAL for joining the laps table:

SELECT 
  races.*, tmptimers.last_start_time, tmplaps. last_updated_at
FROM 
  races
LEFT JOIN 
  timers AS tmptimers 
    ON  tmptimers.user_id = 1 
    AND tmptimers.race_id = races.id
LEFT JOIN LATERAL
  ( SELECT 
      updated_at AS last_updated_at
    FROM 
      laps 
    WHERE 
      user_id = 148
      AND updated_at > '2014-06-13'
      AND race_id = races.id
    ORDER BY 
      updated_at DESC
    LIMIT 1
 ) AS tmplaps
     ON TRUE  
WHERE
  races.account_id = 5
  AND races.id IN (29, 30, ..., 74)
ORDER BY
  last_updated_at DESC ;

I'm not sure what index will be best for the laps table though. Perhaps an index on (user_id, race_id, updated_at). I tested a similar query on SQL-Fiddle and it used the index. The plan showed:

Index Only Scan Backward using "index_name" on laps

You could try simplifying and using LATERAL for joining the laps table:

SELECT 
  races.*, tmptimers.last_start_time, tmplaps. last_updated_at
FROM 
  races
LEFT JOIN 
  timers AS tmptimers 
    ON  tmptimers.user_id = 1 
    AND tmptimers.race_id = races.id
, LATERAL
  ( SELECT 
      updated_at AS last_updated_at
    FROM 
      laps 
    WHERE 
      user_id = 148
      AND updated_at > '2014-06-13'
      AND race_id = races.id
    ORDER BY 
      updated_at DESC
    LIMIT 1
 ) AS tmplaps 
WHERE
  races.account_id = 5
  AND races.id IN (29, 30, ..., 74)
ORDER BY
  last_updated_at DESC ;

I'm not sure what index will be best for the laps table though. Perhaps an index on (user_id, race_id, updated_at).

You could try simplifying and using LATERAL for joining the laps table:

SELECT 
  races.*, tmptimers.last_start_time, tmplaps. last_updated_at
FROM 
  races
LEFT JOIN 
  timers AS tmptimers 
    ON  tmptimers.user_id = 1 
    AND tmptimers.race_id = races.id
LEFT JOIN LATERAL
  ( SELECT 
      updated_at AS last_updated_at
    FROM 
      laps 
    WHERE 
      user_id = 148
      AND updated_at > '2014-06-13'
      AND race_id = races.id
    ORDER BY 
      updated_at DESC
    LIMIT 1
 ) AS tmplaps
     ON TRUE  
WHERE
  races.account_id = 5
  AND races.id IN (29, 30, ..., 74)
ORDER BY
  last_updated_at DESC ;

I'm not sure what index will be best for the laps table though. Perhaps an index on (user_id, race_id, updated_at).

You could try simplifying and using LATERAL for joining the laps table:

SELECT 
  races.*, tmptimers.last_start_time, tmplaps. last_updated_at
FROM 
  races
LEFT JOIN 
  timers AS tmptimers 
    ON  tmptimers.user_id = 1 
    AND tmptimers.race_id = races.id
, LATERAL
  ( SELECT 
      MAX(updated_at) AS last_updated_at
    FROM 
      laps 
    WHERE 
      user_id = 148
      AND updated_at > '2014-06-13'
      AND race_id = races.id
    GROUP BY 
      race_id
 ) AS tmplaps 
WHERE
  races.account_id = 5
  AND races.id IN (29, 30, ..., 74)
ORDER BY
  last_updated_at DESC ;

I'm not sure what index will be best for the laps table though. Perhaps an index on (user_id, race_id, updated_at).

You could try simplifying and using LATERAL for joining the laps table:

SELECT 
  races.*, tmptimers.last_start_time, tmplaps. last_updated_at
FROM 
  races
LEFT JOIN 
  timers AS tmptimers 
    ON  tmptimers.user_id = 1 
    AND tmptimers.race_id = races.id
, LATERAL
  ( SELECT 
      updated_at AS last_updated_at
    FROM 
      laps 
    WHERE 
      user_id = 148
      AND updated_at > '2014-06-13'
      AND race_id = races.id
    ORDER BY 
      updated_at DESC
    LIMIT 1
 ) AS tmplaps 
WHERE
  races.account_id = 5
  AND races.id IN (29, 30, ..., 74)
ORDER BY
  last_updated_at DESC ;

I'm not sure what index will be best for the laps table though. Perhaps an index on (user_id, race_id, updated_at).