Re: Query for filtering records

> SELECT * ...
> FROM ...
> WHERE NOT IN (SELECT contactnum FROM groups WHERE groupnum='c' or
> groupnum='d' OR ... )
>
> is bound to be _much_ faster!

Yeah, that's an obvious optimization. Unfortunately, due to needing to match semantics of a previous non-sql version and some pathological group specifications from clients, it can't be applied as often as I'd like. Yes, I call client specs pathological sometimes. No, they don't know that.

> And even better is
>
> SELECT *
> FROM ... contacts c1
> WHERE NOT EXISTS (SELECT * FROM groups WHERE groupname='c' or
> groupnum='d' or groupnume='e' ... AND groups.contactnum=c1.contactnum)
>
>
> EXISTS is almost always faster in PG.

Exists seems to be the answer. It even gives the right answer, which has been a problem for queries of this sort.

Rewriting the queries so that the subtraction clauses use exists are giving me reasonable runtimes (~5 sec) and believable answers. (instead of the other two extremes of 5 minutes and either no answers or everyone in the database) What's useful here is that I'm getting multiple exists index scans instead of nested loops or table scans. What's more, exists clauses are really easy to integrate into my query generation routine.

thanks

eric