Re: best way to query

Daniel Verite wrote:
> Steve Clark wrote:
>
>
>>any way i have 2 table - A and B.
>>each table has a key field and if a row is in B it should have a
>>corresponding row in A - but theres
>>the problem it doesn't for all the rows in B.
>>
>>So I want to do something like
>>delete from B where key not in (select key from A order by key);
>>
>>The problem is there are about 1,000,000 rows in A and 300,000 rows
>
> in
>
>>B. I let the above run
>>all night and it was still running the next morning. Does anyone have
>
>
>>an idea of a better way.
>
>
> An outer join is sometimes spectacularly more efficient for this
> particular kind of query.
>
> I'd suggest you try:
>
> delete from B where key in
> (select B.key from B left outer join A on A.key=B.key
> where A.key is null)
>
WOW!

this runs in about 10 seconds - thanks Daniel.

explain select count(*) from t_event_ack_log where event_log_no in
(select t_event_ack_log.event_log_no from t_event_ack_log left outer
join t_unit_event_log a on a.event_log_no=t_event_ack_log.event_log_no
where a.event_log_no is null);
QUERY PLAN

----------------------------------------------------------------------------------------------------------------
Aggregate (cost=128349.56..128349.57 rows=1 width=0)
-> Hash Join (cost=94512.91..126935.36 rows=565681 width=0)
Hash Cond: (public.t_event_ack_log.event_log_no =
public.t_event_ack_log.event_log_no)
-> Seq Scan on t_event_ack_log (cost=0.00..14759.85
rows=565685 width=4)
-> Hash (cost=92609.85..92609.85 rows=152245 width=4)
-> HashAggregate (cost=91087.40..92609.85
rows=152245 width=4)
-> Hash Left Join (cost=57337.95..90380.29
rows=282842 width=4)
Hash Cond:
(public.t_event_ack_log.event_log_no = a.event_log_no)
Filter: (a.event_log_no IS NULL)
-> Seq Scan on t_event_ack_log
(cost=0.00..14759.85 rows=565685 width=4)
-> Hash (cost=40696.09..40696.09
rows=1014309 width=4)
-> Seq Scan on t_unit_event_log a
(cost=0.00..40696.09 rows=1014309 width=4)