X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 3CBEBD1B53A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 13 Aug 2003 03:21:15 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 59536-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 13 Aug 2003 00:19:39 -0300 (ADT)
Received: from filter.mimos.my (filter.mimos.my [192.228.137.70])
	by svr1.postgresql.org (Postfix) with ESMTP id 01FBFD1B58A
	for <pgsql-sql@postgresql.org>; Wed, 13 Aug 2003 00:21:03 -0300 (ADT)
Received: from ew.mimos.my (localhost.localdomain [127.0.0.1])
	by filter.mimos.my (8.11.6/8.11.6) with ESMTP id h7D2jkr31442
	for <pgsql-sql@postgresql.org>; Wed, 13 Aug 2003 10:45:46 +0800
Received: (from root@localhost)
	by ew.mimos.my (8.12.3/8.12.3) id h7D3KxSP035554;
	Wed, 13 Aug 2003 11:20:59 +0800 (MYT) (envelope-from wahab@mimos.my)
Received: from mimos.my (sol-wel197.nat.mimos.my [10.1.23.197])
	by ew.mimos.my (8.12.3/8.11.6) with ESMTP id h7D3Kstv035228;
	Wed, 13 Aug 2003 11:20:55 +0800 (MYT) (envelope-from wahab@mimos.my)
Message-ID: <3F39AE9A.5020901@mimos.my>
Date: Wed, 13 Aug 2003 11:20:58 +0800
From: Abdul Wahab Dahalan <wahab@mimos.my>
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US;
	rv:1.0.2) Gecko/20030208 Netscape/7.02
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org, wahab@mimos.my
Subject: How to speeed up the query performance
X-scanner: scanned by Inflex 1.0.10 - (http://pldaniels.com/inflex/)
Content-Type: multipart/alternative;
	boundary="------------060608040208050302060201"
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/124
X-Sequence-Number: 14740

--------------060608040208050302060201
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

How do I speed up the quey performance if I've a query like this :
Does 'not in' command will affected the performance?.

select      
ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber 

from       transportsetup ts
where     ts.bizid = 'B126'
and         ts.deletestatus = 0
and         ts.transportid not in (    select t.transportid
                                                  f rom transportsetup 
t,servicedetail s,logisticservice l
                                                  where t.bizid=l.bizid
                                                      and 
l.serviceid=s.serviceid
                                                      and 
t.transportid=s.transportid
                                                      and t.bizid = 'B126'
                                                      and l.status='Pending'
                                                  or t.bizid=l.bizid
                                                      and 
l.serviceid=s.serviceid
                                                      and 
t.transportid=s.transportid
                                                      and t.bizid = 
'B126' and l.status='Reserved' )
order by ts.transporttype;


Any help pretty much appreciated. Thanks

--------------060608040208050302060201
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <title></title>
</head>
<body>
How do I speed up the quey performance if I've a query like this :<br>
Does '<u><big>not in</big></u>' command will affected the performance?.<br>
<br>
select&nbsp; &nbsp;&nbsp;&nbsp; ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber
<br>
from&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; transportsetup ts<br>
where &nbsp;&nbsp;&nbsp; ts.bizid = 'B126' <br>
and &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ts.deletestatus = 0 <br>
and &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ts.transportid <u><big>not in</big></u> (&nbsp;&nbsp;&nbsp; select t.transportid
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; f rom transportsetup t,servicedetail
s,logisticservice l <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; where t.bizid=l.bizid <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; and l.serviceid=s.serviceid
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; and t.transportid=s.transportid
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; and t.bizid = 'B126'
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; and l.status='Pending'<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; or t.bizid=l.bizid <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; and l.serviceid=s.serviceid
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; and t.transportid=s.transportid
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; and t.bizid = 'B126'
and l.status='Reserved' ) <br>
order by ts.transporttype;<br>
<br>
<br>
Any help pretty much appreciated. Thanks<br>
</body>
</html>

--------------060608040208050302060201--