0

These two queries:

SELECT username, location, task, ts_next - ts AS diff
FROM (
        SELECT username, location, task, ts, (
                SELECT ts AS ts_next
                FROM switches
                WHERE username = a.username
                AND ts > a.ts
                ORDER BY ts ASC LIMIT 1
        )
        FROM switches a
        WHERE task IS NOT NULL
) c
ORDER BY username, ts
;

and

SELECT username, location, task, ts_next - ts AS diff
FROM (
        SELECT username, location, task, ts
        FROM switches
        WHERE task IS NOT NULL
) a
LEFT JOIN LATERAL (
        SELECT ts AS ts_next
        FROM switches
        WHERE username = a.username
        AND ts > a.ts
        ORDER BY ts ASC LIMIT 1
) b
ON true
ORDER BY username, ts
;

give the same results.

When and why should I use one form over the other?

(The switches table is from a time/task tracking service, where only task switches are recorded, but task durations need to be calculated.)


The query plans are different, is one better than the other?

                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Sort  (cost=27.96..28.01 rows=19 width=37)
   Sort Key: a.username, a.ts
   ->  Seq Scan on switches a  (cost=0.00..27.55 rows=19 width=37)
         Filter: (task IS NOT NULL)
         SubPlan 1
           ->  Limit  (cost=1.38..1.38 rows=1 width=8)
                 ->  Sort  (cost=1.38..1.39 rows=4 width=8)
                       Sort Key: switches.ts
                       ->  Seq Scan on switches  (cost=0.00..1.36 rows=4 width=8)
                             Filter: ((ts > a.ts) AND ((username)::text = (a.username)::text))
(10 rows)


                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Sort  (cost=28.34..28.39 rows=19 width=45)
   Sort Key: switches.username, switches.ts
   ->  Nested Loop Left Join  (cost=1.38..27.93 rows=19 width=45)
         ->  Seq Scan on switches  (cost=0.00..1.24 rows=19 width=37)
               Filter: (task IS NOT NULL)
         ->  Limit  (cost=1.38..1.38 rows=1 width=8)
               ->  Sort  (cost=1.38..1.39 rows=4 width=8)
                     Sort Key: switches_1.ts
                     ->  Seq Scan on switches switches_1  (cost=0.00..1.36 rows=4 width=8)
                           Filter: ((ts > switches.ts) AND ((username)::text = (switches.username)::text))
(10 rows)
  • 3
    The first variant is only possible for a single column and a scalar value. If you remove LIMIT 1 from both queries, the first one can be invalid – Lennart May 2 at 14:35
1

Using the subquery version would become awkward if you wanted to return more than one column or more than one row from the innermost subquery. If you never want to do that, then I find the first form easier to read. (Or at least it would be if you removed the outermost subquery and reformatted a bit--can't the expression and the ORDER BY be done directly in the middle query "c"?)

The difference in plan between the two is unlikely to have any practical significance.

  • Yes, the ORDER BY could be done in the middle query, but I couldn't make the subtraction work there. – fadedbee May 3 at 9:16

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.