From jasiek@klaster.net Thu Mar  6 06:41:55 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66])
	by postgresql.org (Postfix) with ESMTP id 60A13475F39
	for <pgsql-sql@postgresql.org>; Thu,  6 Mar 2003 01:41:55 -0500 (EST)
Received: from lamer.pl (localhost [127.0.0.1])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id 4B2622B898; Thu,  6 Mar 2003 07:39:53 +0100 (CET)
From: "Tomasz Myrta" <jasiek@klaster.net>
To: "James Cooper" <jim@luckydigital.com>,
	"sql" <pgsql-sql@postgresql.org>
Subject: Re: INTERSECT / where id IN (etc..)
Date: Thu, 6 Mar 2003 07:39:53 +0800
Message-Id: <20030306073953.M77564@klaster.net>
In-Reply-To: <003701c2e37a$fe0bb2d0$c900a8c0@jax>
References: <003701c2e37a$fe0bb2d0$c900a8c0@jax>
X-Mailer: Open WebMail 1.62 20020221
X-OriginatingIP: 217.96.97.171 (jasiek)
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-2
X-Archive-Number: 200303/165
X-Sequence-Number: 12205

> Greetings.
> 
> I was wondering if somone out there could shed some light on a query 
> performance issie I have. I have a large lookup table that I query 
> with nested selects to gain the INTERSECT result from.
> 
> the bigger this table gets the slower the performance.
> 
> 1. is creating a temp table and only intersecting from this a good idea?
> 
> 2. does having a where clause in your select before your nested 
> intersects change the amount of work the query
> 
> i.e. select person_id from person where person_id < 5000 And 
> person_id IN    as opposed to    select person_id from person where 
> person_id IN(etc)
> 
> thoughts?
person_id<5000 probably won't help - postgres will use index on exact 
person_id if possible.
Anyway - for large queries try change IN (...) into EXISTS (...), or just 
into explicit join if possible. IN clause is rather slow for bigger amount of 
data.

Regards,
Tomasz Myrta




From jim@luckydigital.com Thu Mar  6 00:54:49 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from luckydigital.com (www.luckydigital.com [203.109.157.4])
	by postgresql.org (Postfix) with ESMTP id 48491476022
	for <pgsql-sql@postgresql.org>; Wed,  5 Mar 2003 19:54:49 -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 h260soh01034
	for <pgsql-sql@postgresql.org>; Thu, 6 Mar 2003 13:54:50 +1300
Message-ID: <003701c2e37a$fe0bb2d0$c900a8c0@jax>
From: "James Cooper" <jim@luckydigital.com>
To: "sql" <pgsql-sql@postgresql.org>
Subject: INTERSECT / where id IN (etc..)
Date: Thu, 6 Mar 2003 13:54:50 +1300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0034_01C2E3E7.F4C1B040"
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-Archive-Number: 200303/163
X-Sequence-Number: 12203

This is a multi-part message in MIME format.

------=_NextPart_000_0034_01C2E3E7.F4C1B040
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Greetings.

I was wondering if somone out there could shed some light on a query perfor=
mance issie I have.
I have a large lookup table that I query with nested selects to gain the IN=
TERSECT result from.

the bigger this table gets the slower the performance.

1. is creating a temp table and only intersecting from this a good idea?
2. does having a where clause in your select before your nested intersects =
change the amount of work the query

i.e. select person_id from person where person_id < 5000 And person_id IN
    as opposed to
    select person_id from person where person_id IN(etc)

thoughts?

------=_NextPart_000_0034_01C2E3E7.F4C1B040
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>Greetings.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I was wondering if somone out there could =
shed some=20
light on a query performance issie I have.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I have a large lookup table that I query w=
ith=20
nested selects to gain the INTERSECT result from.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>the bigger this table gets the slower the=
=20
performance.</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>1. is creating a temp table and only=20
intersecting&nbsp;from this a good idea?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>2. does having a where clause in your sele=
ct before=20
your nested intersects change the amount of work the query</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>i.e. select person_id from person where pe=
rson_id=20
&lt; 5000 And person_id IN</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; as opposed to</FONT></D=
IV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; select person_id from p=
erson=20
where person_id IN(etc)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>thoughts?</FONT></DIV>
<DIV>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_0034_01C2E3E7.F4C1B040--