Re: [GENERAL] null and = - Mailing list pgsql-general

Lincoln Yeoh wrote:

> At 10:39 PM 05-12-1999 -0500, Mike Mascari wrote:
> >I would just do:
> >
> >SELECT users.usr_id, users.usr_login FROM users WHERE NOT EXISTS
> > (SELECT wm_accounts.usr_id FROM wm_accounts WHERE
> >  wm_accounts.usr_id = users.usr_id);
>
> OK, my current query is
> select usr_id,usr_login from users where usr_id not in (select userid from
> wm_accounts);
>
> Your query on small test tables (after vacuum analyze):
> Seq Scan on users  (cost=1.83 rows=25 width=16)
>
>   SubPlan
>     ->  Seq Scan on wm_accounts  (cost=1.33 rows=2 width=4)
>
> My query:
> Seq Scan on users  (cost=1.83 rows=25 width=16)
>
>   SubPlan
>     ->  Seq Scan on wm_accounts  (cost=1.33 rows=10 width=4)
>
> What does rows mean? But it looks like your query is better :). Don't fully
> understand why tho. Would it work if wm_accounts is empty?

Yes, certainly. If you envision what the backend is doing, for NOT EXISTS, its
something like this:

for each users record
    perform an index or sequential lookup in wm_accounts for a matching usr_id:
        found:        continue
        not found:  output usr_id
next users record

If, however, you use the NOT IN clause, it looks more like this:

for each users record
    for each wm_accounts record
        if users.usr_id = wm_accounts.usr_id, continue to next users record
    next wm_accounts record
    output usr_id
next users record

At least with the EXISTS/NOT EXISTS method, you give the backend the opportunity
to use indexes on the correlated table. Most commercial databases will
instantiate a temporary table when processing IN clauses and will rewrite the
query as an EXISTS (or DISTINCT join/outer join). PostgreSQL doesn't do that at
the moment. What indexes do you have on users and wm_accounts? You should have
one on usr_id of both. It may simply be that the optimizer isn't using indexes
since the number of rows is small. Here is a pseudo-equivalent explain plan from
a production database:

explain select webuser from webusers where not exists (
select permitbuy.webuser from permitbuy where webusers.webuser =
permitbuy.webuser);

NOTICE:  QUERY PLAN:

Seq Scan on webusers  (cost=7.78 rows=145 width=12)
  SubPlan
    ->  Index Scan using k_permitbuy1 on permitbuy  (cost=4.36 rows=48 width=12)

EXPLAIN

vs. using IN:

explain select webuser from webusers where webuser not in (select webuser from
permitbuy);

NOTICE:  QUERY PLAN:

Seq Scan on webusers  (cost=7.78 rows=145 width=12)
  SubPlan
    ->  Seq Scan on permitbuy  (cost=32.05 rows=759 width=12)

EXPLAIN

Hope that helps,

Mike