Bad plan

Okay, maybe it is just me, but I think that something is wrong with the
way a plan is generated for the following update:

EXPLAIN UPDATE v SET nl=nl+1 WHERE id IN (SELECT sid FROM l WHERE did =
123456) ;

NOTICE:  QUERY PLAN:

Seq Scan on v  (cost=0.00..1884077041.93 rows=2873155 width=38)
  SubPlan
    ->  Materialize  (cost=327.85..327.85 rows=81 width=4)
          -> Index Scan using l_pkey on l (cost=0.00..327.85 rows=81
width=4)

EXPLAIN

If I have static values in the IN(...) clause, it uses the 'v_pkey' index.
I know this because I have tried it.  The only way to make this work the way
I want is to select all 'sid' from 'l' to my application server, then
build the update with static values, and execute it.  For large data sets
(some 'did' have 20K+ 'sid'), it takes a while to download all the rows,
and then send it back.  Also, there is a limitation somewhere around
10,000 values for the IN(...) clause which means the app server has to
send multiple UPDATEs.

I would think the planner could be smarter about this, especially given
that 'id' is the primary key for 'v', and 'l_pkey' is '(did, sid)'.  So,
the planner should know that for any 'did', there will be no duplicate
'sid', and each 'sid' is tied to a specific 'id' in 'v'.

Alternatively, there might be a better way to write this query.  Any
ideas?  I can't think of any way to use EXISTS that wouldn't result in a
sequential scan of the data set.

- brian


Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"