Postgres Pro
Downloads
Home   >   mailing lists

Thread: NOT IN query issues

NOT IN query issues

From
"Booth, Robert"
Date:
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

Re: NOT IN query issues

From
Darren Ferguson
Date:
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:

> 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)
>

Re: NOT IN query issues

From
Jean-Luc Lachance
Date:
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:
>
> 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)