Re: [GENERAL] delete with index scan

2004-11-30 Thread Sally Ruggero 
Thank you so much, I guess my sql skills need sharpening. I have been trying
all night to find out how to do a join with delete. I thought I needed to
mention t2 in the from clause, but that didn't work. This worked great.

Sally

- Original Message - 
From: "Martijn van Oosterhout" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, November 30, 2004 10:38 AM
Subject: Re: [GENERAL] delete with index scan




---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] delete with index scan

2004-11-30 Thread Martijn van Oosterhout 
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, [EMAIL PROTECTED] 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: [EMAIL PROTECTED]
> 
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend

-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   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.


pgp4KTT50adyK.pgp
Description: PGP signature

[GENERAL] delete with index scan

2004-11-30 Thread sar 
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: [EMAIL PROTECTED]


---(end of broadcast)---
TIP 8: explain analyze is your friend