2

I have these two queries with similar Query Plan outputs:

update cartfund A
set "CartId" = (select id from cart where "UserId" = 6)
from cart B
where A."CartId" = B.id and
B."GuestUserId" = 1139;

Query Plan for above:

Update on cartfund a  (cost=16.61..44.36 rows=1 width=43)
   InitPlan 1 (returns $0)
     ->  Index Scan using "cart_UserId_key" on cart  (cost=0.28..8.30 rows=1 width=4)
           Index Cond: ("UserId" = 6)
   ->  Hash Join  (cost=8.31..36.06 rows=1 width=43)
         Hash Cond: (a."CartId" = b.id)
         ->  Seq Scan on cartfund a  (cost=0.00..22.90 rows=1290 width=41)
         ->  Hash  (cost=8.30..8.30 rows=1 width=10)
               ->  Index Scan using "cart_GuestUserId_key" on cart b  (cost=0.28..8.30 rows=1 width=10)
                     Index Cond: ("GuestUserId" = 1139)

Similarly this query does the same task:

with usercart as (select id from cart where "UserId" = 6)
update cartfund a
set "CartId" = c.id
from cart b, usercart c
where a."CartId" = b.id and
b."GuestUserId" = 1139;

And has this query plan:

Update on cartfund a  (cost=16.61..44.39 rows=1 width=75)
   CTE usercart
     ->  Index Scan using "cart_UserId_key" on cart  (cost=0.28..8.30 rows=1 width=4)
           Index Cond: ("UserId" = 6)
   ->  Nested Loop  (cost=8.31..36.09 rows=1 width=75)
         ->  Hash Join  (cost=8.31..36.06 rows=1 width=43)
               Hash Cond: (a."CartId" = b.id)
               ->  Seq Scan on cartfund a  (cost=0.00..22.90 rows=1290 width=41)
               ->  Hash  (cost=8.30..8.30 rows=1 width=10)
                     ->  Index Scan using "cart_GuestUserId_key" on cart b  (cost=0.28..8.30 rows=1 width=10)
                           Index Cond: ("GuestUserId" = 1139)
         ->  CTE Scan on usercart c  (cost=0.00..0.02 rows=1 width=32)

Which one should I use? What are the performance trade-offs? Thanks.

myusuf
  • 11,810
  • 11
  • 35
  • 50
  • If I read correctly, these two queries aren't even doing the same thing logically. The second one involves a table `usercart` which isn't present in the first. – Tim Biegeleisen Feb 23 '17 at 13:06
  • 1
    @TimBiegeleisen usercart is an alias for subquery `(select id from cart where "UserId" = 6)` – myusuf Feb 23 '17 at 13:10
  • I think it does not mater. The only thing which is strage in this update is the (select id from cart where "UserId" = 6) part. Is this valu gona change? If the "UserId" = 6 and the "GuestUserId" = 1139 is hard coded why dont you hard code the id too? – PeterRing Feb 23 '17 at 13:15
  • @PeterRing Thanks. Yes, the values will change. They'll be provided by the app code. Are you sure about both the queries being the same ? – myusuf Feb 23 '17 at 14:04
  • WITH is a barrier for the optimizer, sometimes it is better than a subquery sometimes not :) – greg Feb 23 '17 at 14:12

0 Answers0