Re: Bad plan

Brian-

I'm not sure if this will help the performance, but I believe this statement
is equivalent:

update v set nl=nl+1 where exists (select 'x' from l where l.sid = v.id and
l.did = 123456);

-Nick

--------------------------------------------------------------------------
Nick Fankhauser nickf(at)ontko(dot)com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/

> -----Original Message-----
> From: pgsql-admin-owner(at)postgresql(dot)org
> [mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of Brian McCane
> Sent: Friday, April 19, 2002 3:55 PM
> To: pgsql-admin(at)postgresql(dot)org
> Subject: [ADMIN] 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"
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>