Need an explanation

From: Alan Nilsson <anilsson(at)apple(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Need an explanation
Date: 2013-05-08 01:53:47
Message-ID: 5B6A621D-2E5E-4CB9-9963-D28A96ADA0FC@apple.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2013-05-08 02:30:46 Re: Need an explanation
Previous Message Sahagian, David 2013-05-07 21:03:16 help with log entries during restart