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