Setup: PostgreSQL 9.3.5
I have two tables with a common identifier (
propid) that is unique in one table (
property) and not unique in the second table (
addressstring). There are no NULL
propid in either table [UPDATE: not true, there were NULL in
property.propid], and datatype is INTEGER in both tables.
The following queries - which I assert are functionally identical - return different results:
select distinct propid from addressstring where propid not in (select distinct propid from property); -- returns zero rows
select distinct propid from addressstring except select distinct propid from property; -- returns 1478 rows, all non-NULL
The problem persisted after VACUUM ANALYZE (I did that just in case, although the tables have not been altered since import, except for the creation of a GIST index on a geometry column in
When I feed Query 2 as a CTE and look for the 1478 identified
propid in the
property table, sure enough they ain't there -
select propid from property where propid in (select distinct propid from addressstring except select distinct propid from property); -- returns zero rows
That's as expected, but frankly I can't trust that result given that Query 1 returns zero rows when it should return 1478.
Three part question:
- am I wrong in assuming that the two queries are functionally identical?
- If I'm not wrong, is there any sensible, defensible explanation for the discrepancy?
- do I have to re-write every
select ... where ... not in (select...)in the
EXCEPTstructure just to be safe?
That last one, if 'Y', is a gigantic PITA given that
select... EXCEPT ... select is not the 'usual' way that most folks get 'A not in B', because often the
NOT IN clause is a subset of the set of variables in the primary
SELECT - e.g.,
SELECT a.objectid AS addressoid, a.propid, b.objectid AS propertyoid FROM address a JOIN property b ON a.propid=b.propid WHERE b.propid NOT IN (SELECT propid WHERE [some condition]);
And yes, the
WHERE clause could be written as
WHERE NOT EXISTS (SELECT 1 [etc]), but the discrepancy in results is a BIG problem given that the query using
NOT IN took a third of a second and the query structure is canonical by the definition in TFD (it is a 'condition that evaluates to a boolean').
Bear in mind - this is not an efficiency issue in the sense that one formulation takes longer than another, valid formulation. This is the fact that two formulations that are set-theoretically identical, produce very different result sets.
UPDATE: the Query Plans produced by EXPLAIN ANALYZE are as follows -
HashAggregate (cost=21999.81..22497.30 rows=49749 width=4) (actual time=256.699..256.699 rows=0 loops=1)" -> Seq Scan on nclm_addressstring_2015 (cost=11521.03..21738.10 rows=104683 width=4) (actual time=256.646..256.646 rows=0 loops=1) Filter: (NOT (hashed SubPlan 1))" Rows Removed by Filter: 209366 -- <-- Removed 100% of rows... WHY????????? SubPlan 1 -> HashAggregate (cost=9427.01..11102.22 rows=167521 width=4) (actual time=91.192..123.690 rows=166742 loops=1) -> Seq Scan on nclm_property_2015 (cost=0.00..9008.21 rows=167521 width=4) (actual time=0.006..40.914 rows=167521 loops=1) Total runtime: 261.303 ms
HashSetOp Except (cost=10217.08..25651.99 rows=99497 width=4) (actual time=334.126..336.933 rows=1478 loops=1) -> Append (cost=10217.08..24984.45 rows=267018 width=4) (actual time=133.840..282.611 rows=334668 loops=1) -> Subquery Scan on *SELECT* 1 (cost=10217.08..12207.01 rows=99497 width=4) (actual time=133.840..174.463 rows=167926 loops=1) -> HashAggregate (cost=10217.08..11212.05 rows=99497 width=4) (actual time=133.838..166.212 rows=167926 loops=1) -> Seq Scan on nclm_addressstring_2015 (cost=0.00..9693.66 rows=209366 width=4) (actual time=0.013..78.953 rows=209366 loops=1) -> Subquery Scan on *SELECT* 2 (cost=9427.01..12777.43 rows=167521 width=4) (actual time=59.971..100.664 rows=166742 loops=1) -> HashAggregate (cost=9427.01..11102.22 rows=167521 width=4) (actual time=59.969..92.796 rows=166742 loops=1) -> Seq Scan on nclm_property_2015 (cost=0.00..9008.21 rows=167521 width=4) (actual time=0.005..26.279 rows=167521 loops=1) Total runtime: 343.926 ms