From: Scott Carey <scott@richrelevance.com>
To: ??????? ???????? <lebiathan@gmail.com>
To: Mladen Gogala <mladen.gogala@vmsinfo.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: "SELECT .. WHERE NOT IN" query running for hours
Date: Mon, 10 Jan 2011 12:24:10 -0800
Message-ID: <C950A6CB.18626%scott@richrelevance.com> (raw)
In-Reply-To: <AANLkTimCc6U-xHigzNcjF3xABadg-yKV_1+kTqXRXhnL@mail.gmail.com>
On 1/7/11 1:29 AM, "??????? ????????" <lebiathan@gmail.com> wrote:
>
>So my follow-up question on the subject is this:
>
>Are there any particular semantics for the "NOT IN" statement that cause
>the correlated query to execute for every row of the outter query, as
>opposed to the "NOT EXISTS" ?
=> select * from explode_array(ARRAY[1,2,3,4,5]) where explode_array not
in (0, 1, 2);
explode_array
---------------
3
4
5
=> select * from explode_array(ARRAY[1,2,3,4,5]) where explode_array not
in (0, 1, 2, null);
explode_array
---------------
(0 rows)
The existence of a single NULL in the "not in" segment causes no results
to be returned. Postgres isn't smart enough to analyze whether the
contents of the NOT IN() can contain null and choose a more optimal plan,
and so it always scans ALL rows. Even if the NOT IN() is on a not null
primary key. NOT IN is generally dangerous because of this behavior --
it results from the fact that '1 = null' is null, and 'not null' is equal
to 'null':
=> select (1 = 1);
?column?
----------
t
select NOT (1 = 1);
?column?
----------
f
=> select (1 = null);
?column?
----------
(1 row)
=> select NOT (1 = null);
?column?
----------
(1 row)
NOT EXISTS doesn't have this problem, since NOT EXISTS essentially treats
the existence of null as false, where NOT IN treats the existence of null
as true.
rr=> select * from (select * from explode_array(ARRAY[1,2,3,4,5])) foo
where not exists (select 1 where explode_array in (0, 1, 2, null));
explode_array
---------------
3
4
5
(3 rows)
Often, the best query plans result from 'LEFT JOIN WHERE right side is
NULL' rather than NOT EXISTS however. I often get performance gains by
switching NOT EXISTS queries to LEFT JOIN form. Though sometimes it is
less performant.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott@richrelevance.com, lebiathan@gmail.com, mladen.gogala@vmsinfo.com
Subject: Re: "SELECT .. WHERE NOT IN" query running for hours
In-Reply-To: <C950A6CB.18626%scott@richrelevance.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox