Re: NOT IN doesn't use index? (fwd)

I think that list is actually (gulp) hard coded.  It's not my query.  I am
trying to speed it up for someone else - to hopefully learn something in
the process that isn't dependent on what version of postgres i'm
running :)

I assume it's from another table but can't find it on their data model at
the moment.  Those are all valid billing codes.  The query is checking to see if
anyone was billed under an invalid code.  So if everything is ok, the query
returns nothing.

But there must be more to it than that...otherwise, they could just add a
Valid flag to the lookup table.

If you have any ideas for speeding it up other than using another table
please let me know.  It only takes me 9 min to run with 2 mil rows but it
takes them 7 hours (51 mil rows in Oracle with many other jobs running and
poor system maintenance.)



 On Sat, 3 May 2003, Joe Conway
wrote:

> Becky Neville wrote:
> > Well I think you answered my question already, but just in case
> > here are the explain results again and the query follows (I warned, it is
> > long.)  And I did run VACUUM ANALYZE beforehand.
>
> [snipped ugly query with three NOT IN clauses]
>
> Hmmm, no surprise that's slow. How are those three lists of constants
> generated? One idea is to recast this as a left join with a FROM clause
> subselect, e.g.
>
> select
>   uabopen_srat_code
> from
>   uabopen u left join
>   (select '1F' as uabopen_srat_code union all
>           '1FD' union all
>           '3A' ...) as ss
>   on u.uabopen_srat_code = ss.uabopen_srat_code
> where ss.uabopen_srat_code is null;
>
> But I'm not sure that will be much quicker. If the list of
> uabopen_srat_code you're filtering on comes from one of the other
> tables, you might be able to do better -- back to the question above,
> how is that list generated? What do the other table look like?
>
> Joe
>