From: James Cooper <jim@luckydigital.com>
To: sql <pgsql-sql@postgresql.org>
Subject: performance
Date: Wed, 26 Feb 2003 16:09:40 +1300
Message-ID: <006801c2dd44$80e103a0$c900a8c0@jax> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jim@luckydigital.com
Subject: Re: performance
In-Reply-To: <006801c2dd44$80e103a0$c900a8c0@jax>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox