From: | "James Cooper" <jim(at)luckydigital(dot)com> |
---|---|
To: | "sql" <pgsql-sql(at)postgresql(dot)org> |
Subject: | performance |
Date: | 2003-02-26 03:09:40 |
Message-ID: | 006801c2dd44$80e103a0$c900a8c0@jax |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Itai Zukerman | 2003-02-26 03:29:11 | OffsetNumber, picksplit, and GiST |
Previous Message | Bob Smith | 2003-02-26 01:31:30 | Followup Re: Postgres locking |