Re: Optimisation of IN condition
От | Rob Arnold |
---|---|
Тема | Re: Optimisation of IN condition |
Дата | |
Msg-id | 002e01c0a825$2f290e50$4200fd0a@home обсуждение исходный текст |
Ответ на | FW: Optimisation of IN condition ("Mayers, Philip J" <p.mayers@ic.ac.uk>) |
Список | pgsql-general |
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 | > +----------------------------------+ >
В списке pgsql-general по дате отправления: