X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id DEFEE474E4F
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 22:09:32 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 58738-08 for <pgsql-sql@postgresql.org>;
	Tue, 25 Feb 2003 22:09:22 -0500 (EST)
Received: from luckydigital.com (www.luckydigital.com [203.109.157.4])
	by postgresql.org (Postfix) with ESMTP id 699C8474E42
	for <pgsql-sql@postgresql.org>; Tue, 25 Feb 2003 22:09:21 -0500 (EST)
Received: from jax (203-109-198-1.akl.ihugultra.co.nz [203.109.198.1])
	by luckydigital.com (8.11.6/8.10.0) with SMTP id h1Q39OT16485
	for <pgsql-sql@postgresql.org>; Wed, 26 Feb 2003 16:09:24 +1300
Message-ID: <006801c2dd44$80e103a0$c900a8c0@jax>
From: "James Cooper" <jim@luckydigital.com>
To: "sql" <pgsql-sql@postgresql.org>
Subject: performance
Date: Wed, 26 Feb 2003 16:09:40 +1300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0065_01C2DDB1.77ADE470"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/571
X-Sequence-Number: 11969

This is a multi-part message in MIME format.

------=_NextPart_000_0065_01C2DDB1.77ADE470
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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=20
( select person_id from cluster_person cp, cluster_target ct, cluster c whe=
re c.cluster_id =3D cp.cluster_id and cp.cluster_id =3D ct.cluster_id and c=
t.target_id =3D 9 and c.cluster_type =3D 2 )
and person_id IN
( select person_id from cluster_person cp, cluster_target ct, cluster c whe=
re c.cluster_id =3D cp.cluster_id and cp.cluster_id =3D ct.cluster_id and c=
t.target_id =3D 9 and c.cluster_type =3D 3 )

-->Seq Scan on person (cost=3D0.00..446281.56 rows=3D1146 width=3D4)

select person_id as tot from person where person_id IN=20
( select person_id from cluster_person cp, cluster_target ct, cluster c whe=
re c.cluster_id =3D cp.cluster_id and cp.cluster_id =3D ct.cluster_id and c=
t.target_id =3D 9 and c.cluster_type =3D 2 )
INTERSECT
( select person_id from cluster_person cp, cluster_target ct, cluster c whe=
re c.cluster_id =3D cp.cluster_id and cp.cluster_id =3D ct.cluster_id and c=
t.target_id =3D 9 and c.cluster_type =3D 3 )

-->SetOp Intersect (cost=3D223522.57..223537.75 rows=3D304 width=3D16)

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=20



------=_NextPart_000_0065_01C2DDB1.77ADE470
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1141" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2><FONT color=3D#0000ff size=3D1>
<P><FONT color=3D#000000 size=3D2>Im toying with performance issues in my q=
ueries at=20
the moment see below:</FONT></P>
<P><FONT color=3D#000000 size=3D2>select count(person_id) as tot from perso=
n where=20
person_id IN <BR>( select person_id from cluster_person cp, cluster_target =
ct,=20
cluster c where c.cluster_id =3D cp.cluster_id and cp.cluster_id =3D ct.clu=
ster_id=20
and ct.target_id =3D 9 and c.cluster_type =3D 2 )<BR>and person_id IN<BR>( =
select=20
person_id from cluster_person cp, cluster_target ct, cluster c where=20
c.cluster_id =3D cp.cluster_id and cp.cluster_id =3D ct.cluster_id and ct.t=
arget_id=20
=3D 9 and c.cluster_type =3D 3 )</FONT></P>
<P><FONT color=3D#000000 size=3D2>--&gt;<FONT size=3D1>Seq Scan </FONT><FON=
T=20
color=3D#008000 size=3D1>on</FONT><FONT size=3D1> person (cost=3D0.00..4462=
81.56=20
rows=3D1146 width=3D4)</P></FONT></FONT>
<P><FONT color=3D#000000 size=3D2>select person_id as tot from person where=
=20
person_id IN <BR>( select person_id from cluster_person cp, cluster_target =
ct,=20
cluster c where c.cluster_id =3D cp.cluster_id and cp.cluster_id =3D ct.clu=
ster_id=20
and ct.target_id =3D 9 and c.cluster_type =3D 2 )<BR>INTERSECT<BR>( select =
person_id=20
from cluster_person cp, cluster_target ct, cluster c where c.cluster_id =3D=
=20
cp.cluster_id and cp.cluster_id =3D ct.cluster_id and ct.target_id =3D 9 an=
d=20
c.cluster_type =3D 3 )</FONT></P>
<P><FONT color=3D#000000 size=3D2>--&gt;<FONT size=3D1>SetOp Intersect=20
(cost=3D223522.57..223537.75 rows=3D304 width=3D16)</FONT></FONT></P>
<P><FONT color=3D#000000 size=3D2><FONT size=3D1><FONT size=3D2>two=20
questions,</FONT></FONT></FONT></P>
<P><FONT color=3D#000000 size=3D2><FONT size=3D1><FONT size=3D2>1- is there=
 a=20
performance difference between "intersect"&nbsp;and "AND person_id=20
IN</FONT></FONT></FONT></P>
<P><FONT color=3D#000000 size=3D2><FONT size=3D1><FONT size=3D2>2 -when I u=
se INTERSECT=20
if I try and use count() like in the first example I get nothing back, why =
is=20
this</FONT></FONT></FONT><FONT color=3D#000000 size=3D2><FONT size=3D1><FON=
T=20
size=3D2>?</FONT></FONT></FONT></P>
<P><FONT color=3D#000000 size=3D2><FONT size=3D1><FONT=20
size=3D2>James</FONT>&nbsp;</P></FONT></FONT>
<P><FONT color=3D#000000 size=3D2></FONT></FONT><FONT=20
size=3D1>&nbsp;</P></FONT></FONT></DIV></BODY></HTML>

------=_NextPart_000_0065_01C2DDB1.77ADE470--