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

Here is the EXPLAIN output from the two queries.  The first is the one
that uses WHERE field NOT IN ( 'a','b' etc ).  The second is the (much
faster) one
that uses WHERE NOT (field = 'a' and field = 'b' etc).

I don't understand why the query planner thinks there are only 38055 rows
in the table on the slow one.  I didn't run analyze in between them and the
second try seems to know (correctly) that there are 1799976 rows.

Also, why does the first (slow) one think there are 38055 rows and only
evaluate 48 rows - and yet it still takes longer. ? I assume it's due to
the lack of a sort, but I don't understand why using NOT IN should
prohibit a sort.

-------------slow one - ~9 minutes-----------------------
/home/accts/ran26/cs437/Proj/code/scripts/sql
test=# \i query3.sql
psql:query3.sql:76: NOTICE:  QUERY PLAN:

Seq Scan on uabopen  (cost=0.00..3305914.86 rows=38055 width=7) (actual
time=36577.26..494243.37 rows=48 loops=1)
Total runtime: 494243.67 msec

--------------faster one - 2 minutes-----------------
psql:query3Mod2.sql:77: NOTICE:  QUERY PLAN:

Unique  (cost=3592408.28..3596908.22 rows=179998 width=7) (actual
time=104959.31..114131.22 rows=101 loops=1)
  ->  Sort  (cost=3592408.28..3592408.28 rows=1799976 width=7) (actual
time=104959.30..108425.61 rows=1799976 loops=1)
        ->  Seq Scan on uabopen  (cost=0.00..3305914.86 rows=1799976
width=7) (actual time=30.13..14430.99 rows=1799976 loops=1)
Total runtime: 114220.66 msec



---------- Forwarded message ----------
Date: Sat, 3 May 2003 13:09:22 -0400 (EDT)
From: Becky Neville <ran26@pantheon.yale.edu>
To: Andrew Sullivan <andrew@libertyrms.info>
Subject: Re: [PERFORM] NOT IN doesn't use index?

I didn't post it because the rest of the query is exactly the same (and
the NOT IN list is about a page long - although it's
apparently still shorter than the IN list.)

I need to verify something and then can send the EXPLAIN output.

I am running my own server and have no idea what parameters I should use
to speed things up.  Everything is dog slow.


On Sat, 3 May 2003, Andrew Sullivan wrote:

> On Sat, May 03, 2003 at 01:56:02AM -0400, Becky Neville wrote:
> > Does the use of WHERE field  NOT IN ('A','B' etc) prevent the use of an
> > index?
>
> That '&c.' is hiding a lot.  Why not post your query and the explain
> analyse output?
>
> A
>