Re: NOT IN doesn't use index? (fwd) - Mailing list pgsql-performance

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.

psql:sql/query3.sql:76: NOTICE:  QUERY PLAN:

Seq Scan on uabopen  (cost=0.00..3305914.86 rows=56580 width=7) (actual
time=36077.26..491592.22 rows=48 loops=1)
Total runtime: 491592.52 msec
-------------------------------------------

explain analyze
select uabopen_srat_code
  FROM UABOPEN
 where uabopen_srat_code not in
('1A','1B','1C','1E','1AC','1BC','1CC','1EC','PG1A',

'PG1B','PG1C','PG1E','R1A','R1B',

'R1C','R1E','RD1A','RD1B','RD1C','RD1E','TRF','WN1A',
                                            'WN1B','WN1C','WN1E', 'APS')
   AND uabopen_srat_code not in
('1F','1FD','3A','3AD','3B','3B1','3BD','3C','3CD','3F',

'3FD','3G','3GD','3H','3HD','4A','4AD','5A','5AD','5B','5BD','5C','5CD',

'5D','5DD','5E','5ED','5F','5FD','5G','5GD','6A','6AD','6B','6BD','6C',

'6CD','6D','6DD','8A','8B','8AD','9A','9TA','9AD','9B','9BD','9C','9CD','9D','9D\
D',

'9E','9ED','9F','9FD','9G','9GD','9H','9I','9T','ACC','CM3A','CM3B','CM3C','CM3F\
',

'CM3G','CM3H','DEM','GR3A','GR3B','GR3C','GR3H','GR4A','GR5A','GR5B','GR5C',

'GR5D','GR5E','GR5F','GR6A','GR6B','GR6C','GR6D','GR9A','GR9B','GR9C','GR9D','GR\
9E',

'GR9F','GR9G','GR9H','GR9T','MT3B','MT3C','MT3G','MT3H','MT4A','MT9A','MT9B','MT\
9C',

'MT9D','MT9E','MT9F','MT9G','N1','N10','N100','N101','N102','N103','N104','N105'\
,

'N106','N107','N108','N109','ITCP','1FC','3AP','3CP','5AC',

'5AP','5BC','5BP','5CC','5CP','5DC','5DP','5GC','6AC','6AP','6BC','6BP','6CC','6\
CP',

'6DC','6DP','MT5A','MT5B','MT6A','MT6B','MT5H','MT6I','MT6H',

'5HP','6H','6HC','6HP','6I','6IC','6IP','3BP','5H','5HC',

'5I','5IC','5IP','GR5H','GR5I','GR6H','GR6I',

'MT5I','PG5H','PG5I','PG6H','PG6I','WN5H','WN5I','WN6H','WN6I',

'5CT','6CT','6DT','MT6C','MT6D','MT5C','MT5D','5DT','5HD')
    AND UABOPEN_SRAT_CODE NOT IN
('N11','N110','N111','N112','N113','N114','N115','N116','N117','N118','N119','N12',

'N120','N121','N122','N123','N124','N125','N126','N127','N128','N129','N13','N13\
0',

'N131','N132','N133','N134','N135','N136','N137','N138','N139','N14','N140',

'N141','N142','N143','N144','N145','N146','N147','N148','N149','N15','N150',

'N151','N152','N153','N154','N155','N156','N157','N158'


On Sat, 3 May 2003, Joe Conway wrote:

> Becky Neville wrote:
> > 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
>
> Unless you are working with Postgres 7.4devel (i.e. cvs HEAD), the IN
> construct is notoriously slow in Postgres. In cvs it is vastly improved.
>
> Also, as I mentioned in the other reply, send in "EXPLAIN ANALYZE"
> results instead of "EXPLAIN" (and make sure you run "VACUUM ANALYZE" first).
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>