How can I make this query more efficient?

Question

This is the query:

SELECT 
  races.*, tmptimers.last_start_time, tmplaps.updated_at AS last_updated_at
FROM 
  races
LEFT JOIN 
  (SELECT * FROM timers WHERE timers.user_id = 1) AS tmptimers ON tmptimers.race_id = races.id
LEFT JOIN
  (
  SELECT 
    race_id, 
    MAX(updated_at) AS updated_at 
  FROM
    (SELECT 
      race_id, updated_at
    FROM 
      laps 
    WHERE 
      user_id = 148
      AND updated_at > '2014-06-13'
    ) AS tmp
    GROUP BY 
    race_id
  ) AS tmplaps ON tmplaps.race_id = races.id
WHERE
  races.account_id = 5
  AND races.id IN (29, 30, 31, 32, 3, 2, 33, 1, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 
    22, 23, 24, 25, 26, 27, 28, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 
    55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 119, 154, 122, 82, 123, 156, 76, 73, 138, 70, 150, 135, 92, 143, 
    75, 72, 126, 113, 80, 83, 77, 94, 87, 129, 117, 68, 104, 134, 148, 152, 111, 120, 141, 69, 158, 96, 116, 114, 
    147, 78, 130, 139, 131, 90, 118, 132, 136, 67, 102, 84, 105, 101, 81, 153, 112, 137, 144, 71, 88, 89, 157, 107, 
    109, 79, 140, 97, 110, 106, 93, 142, 128, 108, 151, 95, 98, 121, 103, 149, 85, 124, 145, 99, 125, 146, 115, 133,
    100, 86, 91, 127, 155, 74)
ORDER BY
  last_updated_at DESC;

And here's the explain analyze for my (rather small) dataset:

   Sort Key: tmplaps.updated_at
   Sort Method: quicksort  Memory: 47kB
   ->  Nested Loop Left Join  (cost=211.16..286.25 rows=158 width=130) (actual time=3.854..4.248 rows=158 loops=1)
         Join Filter: (timers.race_id = races.id)
         ->  Hash Left Join  (cost=211.16..265.50 rows=158 width=122) (actual time=3.852..4.186 rows=158 loops=1)
               Hash Cond: (races.id = tmplaps.race_id)
               ->  Seq Scan on races  (cost=0.00..52.81 rows=158 width=114) (actual time=0.012..0.254 rows=158 loops=1)
                     Filter: ((account_id = 5) AND (id = ANY ('{29,30,31,32,3,2,33,1,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,119,154,122,82,123,156,76,73,138,70,150,135,92,143,75,72,126,113,80,83,77,94,87,129,117,68,104,134,148,152,111,120,141,69,158,96,116,114,147,78,130,139,131,90,118,132,136,67,102,84,105,101,81,153,112,137,144,71,88,89,157,107,109,79,140,97,110,106,93,142,128,108,151,95,98,121,103,149,85,124,145,99,125,146,115,133,100,86,91,127,155,74}'::integer[])))
                     Rows Removed by Filter: 3
               ->  Hash  (cost=209.96..209.96 rows=96 width=12) (actual time=3.833..3.833 rows=96 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 5kB
                     ->  Subquery Scan on tmplaps  (cost=208.03..209.96 rows=96 width=12) (actual time=3.768..3.807 rows=96 loops=1)
                           ->  HashAggregate  (cost=208.03..209.00 rows=96 width=12) (actual time=3.766..3.786 rows=96 loops=1)
                                 ->  Seq Scan on laps  (cost=0.00..182.03 rows=5201 width=12) (actual time=0.005..2.075 rows=5202 loops=1)
                                       Filter: ((updated_at > '2014-06-13 00:00:00'::timestamp without time zone) AND (user_id = 148))
         ->  Materialize  (cost=0.00..18.38 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=158)
               ->  Seq Scan on timers  (cost=0.00..18.38 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=1)
                     Filter: (user_id = 1)
 Total runtime: 4.494 ms

It seems to me that the most critical piece is the SeqScan on laps. Is there anything that can be done to minimize the time it takes?

Answer 1

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

Answer 2

Two more things in addition to what has been said already:

• IN with long lists does not scale well (at least it did not in my tests on Postgres 9.1; have to run new tests ..). I found it to be faster to prepare a derived table and JOIN to it. Details in this related answer on SO.

• If laps is a big table and your query only select a small fraction of rows (information is missing) it is faster to join first and aggregate later. (While it is the other way round when retrieving all or most rows.)

Thus, try:

SELECT r.*, t.last_start_time, MAX(l.updated_at) AS last_updated_at
FROM  (
    SELECT unnest('{29,30,31,32,3,2,33,1,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,
    22,23,24,25,26,27,28,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,
    55,56,57,58,59,60,61,62,63,64,65,66,119,154,122,82,123,156,76,73,138,70,150,135,92,143,
    75,72,126,113,80,83,77,94,87,129,117,68,104,134,148,152,111,120,141,69,158,96,116,114,
    147,78,130,139,131,90,118,132,136,67,102,84,105,101,81,153,112,137,144,71,88,89,157,107,
    109,79,140,97,110,106,93,142,128,108,151,95,98,121,103,149,85,124,145,99,125,146,115,133,
    100,86,91,127,155,74}'::int[])
    ) ids(id)
JOIN   races       r  USING (id)
LEFT   JOIN timers t  ON t.race_id = r.id
                     AND t.user_id = 1
LEFT   JOIN laps   l  ON l.race_id = r.id
                     AND l.user_id = 148
                     AND l.updated_at > '2014-06-13'
WHERE  r.account_id = 5
GROUP  BY r.id  -- must be PK
        , t.last_start_time
ORDER  BY last_updated_at DESC;

Perfect index would be:

CREATE INDEX laps_idx ON laps(user_id, race_id, updated_at DESC)