Postgres Pro
Downloads
Home   >   mailing lists

Thread: Need an explanation

Need an explanation

From
Alan Nilsson
Date:
Could someone explain to me what I'm missing here.  Given the following 3 queries:

mqsql01.automation > select count(asp_id) from asps where asp_id>90000 and asp_id not in (select asp_id from dasp where asp_id>1);
 count 
-------
    84
(1 row)

mqsql01.automation > select count(asp_id) from asps where asp_id>90000 and asp_id not in (select asp_id from dasp);
 count 
-------
     0
(1 row)

mqsql01.automation > select min(asp_id) from dasp;
  min  
-------
 92701
(1 row)


What is going on in the subquery that causes the different results?  If I run an explain on the first 2 queries:

mqsql01.automation > explain select count(asp_id) from asps where asp_id>90000 and asp_id not in (select asp_id from dasp where asp_id>1);
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Aggregate  (cost=3240.72..3240.73 rows=1 width=4)
   ->  Bitmap Heap Scan on asps  (cost=246.29..3238.41 rows=921 width=4)
         Recheck Cond: (asp_id > 90000)
         Filter: (NOT (hashed SubPlan 1))
         ->  Bitmap Index Scan on asps_pkey  (cost=0.00..66.08 rows=1843 width=0)
               Index Cond: (asp_id > 90000)
         SubPlan 1
           ->  Seq Scan on dasp  (cost=0.00..175.15 rows=1931 width=4)
                 Filter: (asp_id > 1)
(9 rows)

mqsql01.automation > explain select count(asp_id) from asps where asp_id>90000 and asp_id not in (select asp_id from dasp);
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Aggregate  (cost=3235.89..3235.90 rows=1 width=4)
   ->  Bitmap Heap Scan on asps  (cost=241.46..3233.59 rows=921 width=4)
         Recheck Cond: (asp_id > 90000)
         Filter: (NOT (hashed SubPlan 1))
         ->  Bitmap Index Scan on asps_pkey  (cost=0.00..66.08 rows=1843 width=0)
               Index Cond: (asp_id > 90000)
         SubPlan 1
           ->  Seq Scan on dasp  (cost=0.00..170.32 rows=1932 width=4)
(8 rows)


I see that the first query, which returns the expected result has an additional filter in the plan.  How does that filter materially affect the outcome?  Since the filter is true for every tuple, I would expect the same result from both queries.

thanks
alan

Re: Need an explanation

From
Tom Lane
Date:
Alan Nilsson <anilsson@apple.com> writes:
> Could someone explain to me what I'm missing here.

Probably there are some NULLs in dasp.asp_id.  NOT IN cannot succeed
when there are any nulls in the sub-select result: per SQL spec, the
outcome of such a test can only be FALSE or NULL.  If that isn't the
behavior you want, try recasting the query to use NOT EXISTS.

            regards, tom lane

Re: Need an explanation

From
Alan Nilsson
Date:
that was indeed the case.  Did not think to look at that, thanks much for the kick :)

alan

On May 7, 2013, at 7:30 PM, Tom Lane wrote:

> Alan Nilsson <anilsson@apple.com> writes:
>> Could someone explain to me what I'm missing here.
>
> Probably there are some NULLs in dasp.asp_id.  NOT IN cannot succeed
> when there are any nulls in the sub-select result: per SQL spec, the
> outcome of such a test can only be FALSE or NULL.  If that isn't the
> behavior you want, try recasting the query to use NOT EXISTS.
>
>             regards, tom lane
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general