NOT IN query issues
I'm trying to run a DELETE query using NOT IN and it's causing me some
problems.
Here is the query that I want to run:
delete from sharinfo where file__no not in (select file__no from
allforms);
When I explain this I get:
Seq Scan on sharinfo (cost=0.00..2225204.88 rows=5239 width=6)
SubPlan
-> Seq Scan on allforms (cost=0.00..424.69 rows=8669 width=12)
Which while a very large number does seem to work. But when I run the query
nothing appears to happen, I've left this query running for hours with no
response back. Is there a better way to run a query like this (deleting all
rows that don't appear in a second table)?
Thanks,
Rob
Might not help but try exists.
Have seen cases where the not in just doesn't seem as optimized
HTH
Darren Ferguson
On Tue, 7 May 2002, Booth, Robert wrote:
Show quoted text
I'm trying to run a DELETE query using NOT IN and it's causing me some
problems.Here is the query that I want to run:
delete from sharinfo where file__no not in (select file__no from
allforms);When I explain this I get:
Seq Scan on sharinfo (cost=0.00..2225204.88 rows=5239 width=6)
SubPlan
-> Seq Scan on allforms (cost=0.00..424.69 rows=8669 width=12)Which while a very large number does seem to work. But when I run the query
nothing appears to happen, I've left this query running for hours with no
response back. Is there a better way to run a query like this (deleting all
rows that don't appear in a second table)?Thanks,
Rob---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Robert,
Try:
delete from sharinfo where not exists (
select file__no from allforms where sharinfo.file__no =
allforms.file__no);
Make sure allforms is indexed on file__no.
"Booth, Robert" wrote:
Show quoted text
I'm trying to run a DELETE query using NOT IN and it's causing me some
problems.Here is the query that I want to run:
delete from sharinfo where file__no not in (select file__no from
allforms);When I explain this I get:
Seq Scan on sharinfo (cost=0.00..2225204.88 rows=5239 width=6)
SubPlan
-> Seq Scan on allforms (cost=0.00..424.69 rows=8669 width=12)Which while a very large number does seem to work. But when I run the query
nothing appears to happen, I've left this query running for hours with no
response back. Is there a better way to run a query like this (deleting all
rows that don't appear in a second table)?Thanks,
Rob---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)