Im toying with performance issues in my queries at the moment see below:
select count(person_id) as tot from person where person_id IN
( select person_id from cluster_person cp, cluster_target ct, cluster c where c.cluster_id = cp.cluster_id and cp.cluster_id = ct.cluster_id and ct.target_id = 9 and c.cluster_type = 2 )
and person_id IN
( select person_id from cluster_person cp, cluster_target ct, cluster c where c.cluster_id = cp.cluster_id and cp.cluster_id = ct.cluster_id and ct.target_id = 9 and c.cluster_type = 3 )
-->Seq Scan on person (cost=0.00..446281.56 rows=1146 width=4)
select person_id as tot from person where person_id IN
( select person_id from cluster_person cp, cluster_target ct, cluster c where c.cluster_id = cp.cluster_id and cp.cluster_id = ct.cluster_id and ct.target_id = 9 and c.cluster_type = 2 )
INTERSECT
( select person_id from cluster_person cp, cluster_target ct, cluster c where c.cluster_id = cp.cluster_id and cp.cluster_id = ct.cluster_id and ct.target_id = 9 and c.cluster_type = 3 )
-->SetOp Intersect (cost=223522.57..223537.75 rows=304 width=16)
two questions,
1- is there a performance difference between "intersect" and "AND person_id IN
2 -when I use INTERSECT if I try and use count() like in the first example I get nothing back, why is this?
James