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