Re: Bad plan
От | Nick Fankhauser |
---|---|
Тема | Re: Bad plan |
Дата | |
Msg-id | NEBBLAAHGLEEPCGOBHDGOEBGENAA.nickf@ontko.com обсуждение исходный текст |
Ответ на | Bad plan (Brian McCane <bmccane@mccons.net>) |
Список | pgsql-admin |
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@ontko.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@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Brian McCane > Sent: Friday, April 19, 2002 3:55 PM > To: pgsql-admin@postgresql.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 >
В списке pgsql-admin по дате отправления: