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(dot)mayers(at)ic(dot)ac(dot)uk>
To: <pgsql-general(at)postgresql(dot)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 |
> +----------------------------------+
>