Thread: performance

performance

From

"James Cooper"

Date:

26 February 2003, 01:09:34

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