Hej, Kjell!

>select m.* from master m
>join detail d
>on (m.id = d.master)
>where d.removed is not null
>and not exists
>(select 1 from detail d2
>where d2.master = m.id
>and d2.removed is null)
>
>But this query would join like 99.9999 percent of the masters with details, and for each one execute the subquery, i.e. about >200 million subquery executions.

It might execute the subselect lots of times (though far from 200 million times, surely not 200 million rows match the criteria D.REMOVED IS NOT NULL?), but it might still be quicker than your 9 hour query.

>I tried this:
>select M."Id"
>from "Master" M
>inner join "Detail" D on D."Master" = M."Id"
>group by M."Id"
>having count(D."Id") > 0
> and count(case when D."Removed" is null then 1 end) = 0

I would guess (I don't know for sure) this query took somewhat longer than the NOT EXISTS version (though I have no clue whether the NOT EXISTS would take 30 minutes or 8 hours 55 minutes). Anyway, with an INNER JOIN, the COUNT(D."Id") > 0 will always return TRUE (provided there are no records in "Detail" where "Id" IS NULL) and could simply be removed.

Generally, a query where there is no indexed field (and no expression index) in the WHERE clause, has to go NATURAL for at least one table. A 'geekish' answer to your original question 'Can anyone figure out a way to avoid a full table scan?' would therefore be: 'Yes, it is possible, but it will be slower than using NATURAL'. I'd probably go for the 'modified Helen version' (alternatively, your original 'obvious query' with the addition AND D."Removed" IS NOT NULL in the EXISTS - at best it will perform like 'modified Helen', but give a more correct result set - 'modified Helen' could return duplicates of there are more than one detail with REMOVED IS NOT NULL).

HTH,
Set