Skip site navigation (1) Skip section navigation (2)

Re: Bad plan

From: "Nick Fankhauser" <nickf(at)ontko(dot)com>
To: "Brian McCane" <bmccane(at)mccons(dot)net>, <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Bad plan
Date: 2002-04-19 21:54:25
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin

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 = and
l.did = 123456);


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

> -----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:
> 123456) ;
> 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)
> 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   | when you knock, equally
> spaced amid those
> Usenet | that open when you don't
> want them to.
> Auction | - Roger Zelazny "Blood of Amber"
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?

In response to

  • Bad plan at 2002-04-19 20:54:40 from Brian McCane

pgsql-admin by date

Next:From: GunnarDate: 2002-04-21 07:35:17
Subject: Connection problem
Previous:From: Brian McCaneDate: 2002-04-19 20:54:40
Subject: Bad plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group