Re: Optimisation of IN condition

select * from arptable where arptable.mac where not exists (select mac from
interface where arptable.mac = interface.mac);

See the chapter in Bruce's book "Subqueries Returning Multiple Columns"

cheers

--rob

----- Original Message -----
From: "Mayers, Philip J" <p.mayers@ic.ac.uk>
To: <pgsql-general@postgresql.org>
Sent: Thursday, March 08, 2001 6:22 AM
Subject: FW: Optimisation of IN condition


>
> I've got some tables:
>
> create table interface (
>   machineid text,
>   mac macaddr,
>   primary key(mac)
> );
>
> create table arptable (
>   router text,
>   interface int2,
>   mac macaddr,
>   ip inet
> );
>
> They're big, 10k rows in interface, maybe 35k in arptable. I want to do
> this:
>
> hdb=> explain select * from arptable where mac not in (select mac from
> interface);
> NOTICE:  QUERY PLAN:
>
> Seq Scan on arptable  (cost=0.00..407762.81 rows=4292 width=48)
>   SubPlan
>     ->  Seq Scan on interface  (cost=0.00..189.96 rows=8796 width=6)
>
> But, of course, that a very expensive task. Now, it seems to me that,
since
> I have an index on mac in interface, I *should* in theory be able to speed
> this up, in the following pseudo-code fashion:
>
> foreach mac in arptable:
>     if lookup(mac,interface_pkey):
>         return *
>
> Do you see what I'm getting at? Can I refashion the query somehow to take
> advantage of that? The converse operation, finding registered machines:
>
> hdb=> explain select interface.mac from arptable,interface where
> interface.mac = arptable.mac;
> NOTICE:  QUERY PLAN:
>
> Nested Loop  (cost=0.00..8838.17 rows=4292 width=12)
>   ->  Seq Scan on arptable  (cost=0.00..97.92 rows=4292 width=6)
>   ->  Index Scan using interface_pkey on interface  (cost=0.00..2.02
rows=1
> width=6)
>
> Is, of course, speedy. How can I take advantage of that. EXCEPT doesn't
seem
> to help:
>
> hdb=> explain select mac from arptable except select interface.mac from
> arptable,interface where interface.mac = arptable.mac;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on arptable  (cost=0.00..37933516.98 rows=4292 width=6)
>   SubPlan
>     ->  Materialize  (cost=8838.17..8838.17 rows=4292 width=12)
>           ->  Nested Loop  (cost=0.00..8838.17 rows=4292 width=12)
>                 ->  Seq Scan on arptable  (cost=0.00..97.92 rows=4292
> width=6)
>                 ->  Index Scan using interface_pkey on interface
> (cost=0.00..2.02 rows=1 width=6)
>
>
> Help!
>
> Regards,
> Phil
>
> +----------------------------------+
> | Phil Mayers, Network Support     |
> | Centre for Computing Services    |
> | Imperial College                 |
> +----------------------------------+
>