Skip site navigation (1) Skip section navigation (2)

Re: NOT IN >2hrs vs EXCEPT < 2 sec.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Kevin Traster <kevin(at)mffais(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: NOT IN >2hrs vs EXCEPT < 2 sec.
Date: 2009-01-29 14:56:41
Message-ID: 28345.1233241001@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Kevin Traster <kevin(at)mffais(dot)com> writes:
>  Unique  (cost=3506.21..303375872.86 rows=71946 width=8)
>    ->  Index Scan using cik_ciknum_idx on cik  (cost=3506.21..303375616.75
> rows=102444 width=8)
>          Filter: (NOT (subplan))
>          SubPlan
>            ->  Materialize  (cost=3506.21..6002.40 rows=186019 width=4)
>                  ->  Seq Scan on owner_cik_master  (cost=0.00..2684.19
> rows=186019 width=4)

It will help some if you raise work_mem enough so you get a "hashed
subplan" there, assuming the NOT IN is on a hashable datatype.

But as was already noted, more work has been put into optimizing
EXCEPT and NOT EXISTS than NOT IN, because the latter is substantially
less useful due to its unintuitive but spec-mandated handling of NULLs.
(And this disparity will be even larger in 8.4.)  We're not going to
apologize for that, and we're not going to regard it as a bug.

			regards, tom lane

In response to

pgsql-performance by date

Next:From: anders.blaagaardDate: 2009-01-29 15:58:37
Subject: Max on union
Previous:From: Gregory StarkDate: 2009-01-29 13:00:16
Subject: Re: NOT IN >2hrs vs EXCEPT < 2 sec.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group