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

Re: delete with index scan

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: sar(at)northelectriccompany(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: delete with index scan
Date: 2004-11-30 15:38:13
Message-ID: 20041130153813.GC30959@svana.org (view raw or flat)
Thread:
Lists: pgsql-general
Have you considered a join?

delete from t1 where pkey = t2.pkey;

Also, it appears you never ANALYZEd t2, maybe that would help?

On Tue, Nov 30, 2004 at 04:55:58AM -0500, sar(at)northelectriccompany(dot)com wrote:
> I have a table t1 with a primary key column pkey, and a table t2, with a primary key column pkey. Is there a way to make the following delete use the indexes?
> 
> delete from t1 where pkey in (select pkey from t2);
> 
> NOTICE:  QUERY PLAN:
> 
> Seq Scan on t1 (cost=0.00..6616238.99 rows=660239 width=6)
>   SubPlan
>     ->  Seq Scan on t2 (cost=0.00..20.00 rows=1000 width=8)
> 
> EXPLAIN
> 
> 
> thanks,
> Sally
> 
> ------------------------------------------
> Sally Ruggero
> Software Development
> 
> North Electric Company, Inc.
> 6131 Falls of Neuse Road, Suite 205
> Raleigh, NC 27609
> 
> Office: (919) 341-6009
> Fax:    (919) 341-6010
> 
> Email: sally(dot)ruggero(at)northelectriccompany(dot)com
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

-- 
Martijn van Oosterhout   <kleptog(at)svana(dot)org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

In response to

Responses

pgsql-general by date

Next:From: Pierre-Frédéric CaillaudDate: 2004-11-30 15:51:37
Subject: Re: change natural column order
Previous:From: Jan WieckDate: 2004-11-30 15:31:41
Subject: Re: Upcoming Changes to News Server ...

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