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"