0

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?

  • Per explain.depesz.com/s/QjK, you're right, most of the time is a seqscan on laps. Is there a composite index on laps(user_id, updated_at)? – Craig Ringer Aug 14 '14 at 3:02
  • A derived table seems redundant,use SELECT race_id,MAX(updated_at) FROM laps WHERE user_id = 148 AND updated_at > '2014-06-13' GROUP BY race_id Also a compsoed index on races(account_id,id) might speed things up.Add last_updated_at column to it if it belongs to the races table. – Mihai Aug 14 '14 at 7:24
  • user_id = 1 ... user_id = 148 Two different IDs in the same query? Typo? Or what is the meaning? – Erwin Brandstetter Aug 14 '14 at 17:37
  • Essential information for optimization: Cardinalities of involved tables. How many different user_id in each table? How many distinct race_id in laps? What percentage of rows is after updated_at > '2014-06-13' in laps? Which are the variable parts of the query? Ideally also: (minimal) table definitions including indexes and constraints. – Erwin Brandstetter Aug 14 '14 at 17:45
4

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

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)
  • "at lest it die not"? That should be, I guess: "at least it does not" – ypercubeᵀᴹ Aug 14 '14 at 19:48
  • 1
    @ypercube: thanks. zombi queries never die! :) – Erwin Brandstetter Aug 14 '14 at 19:50
  • Very good insight, I'll definitely experiment with it. Thank you! – kolrie Aug 14 '14 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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