Return to Question

added 3093 characters in body

Source Link

edited May 2, 2019 at 14:32

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)

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)

Source Link

asked May 2, 2019 at 14:25

fadedbee

When to use lateral joins vs correlated subqueries in Postgres?

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.)

postgresql subquery