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
