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 2718FD1C7F3 for ; Thu, 14 Aug 2003 03:35:43 +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 46223-05 for ; Thu, 14 Aug 2003 00:34:04 -0300 (ADT) Received: from filter.mimos.my (filter.mimos.my [192.228.137.70]) by svr1.postgresql.org (Postfix) with ESMTP id 2F8C2D1C4E1 for ; Thu, 14 Aug 2003 00:35:30 -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 h7E302r17365; Thu, 14 Aug 2003 11:00:02 +0800 Received: (from root@localhost) by ew.mimos.my (8.12.3/8.12.3) id h7E3ZOMt000349; Thu, 14 Aug 2003 11:35:24 +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 h7E3ZGtv000125; Thu, 14 Aug 2003 11:35:16 +0800 (MYT) (envelope-from wahab@mimos.my) Message-ID: <3F3B0379.9030602@mimos.my> Date: Thu, 14 Aug 2003 11:35:21 +0800 From: Abdul Wahab Dahalan 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: Abdul Wahab Dahalan Cc: Stephan Szabo , Christoph Haller , pgsql-sql@postgresql.org Subject: Re: How to speeed up the query performance References: <20030813201031.D67777-100000@megazone.bigpanda.com> <3F3B006D.4000708@mimos.my> X-scanner: scanned by Inflex 1.0.10 - (http://pldaniels.com/inflex/) Content-Type: multipart/alternative; boundary="------------060402000304010603040002" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/167 X-Sequence-Number: 14783 --------------060402000304010603040002 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Thanks Chris and Stephan the query is working now. Abdul Wahab Dahalan wrote: > I cant find where to put the parenthesis as u said Stephan. > > Here the query : > > 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 NOT EXISTS (select t.transportid > from transportsetup t,servicedetail s,logisticservice l > where ts.transportid = t.transportid > and 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; > > > > Stephan Szabo wrote: > >>On Thu, 14 Aug 2003, Abdul Wahab Dahalan wrote: >> >> >> >>>Hai Chris! >>>Thanks for the solution but seem it doesnt work. >>>(0 rows) returned when I used NOT EXITS but (4 rows) returned >>>when NOT IN is used................... >>> >>> >> >>Maybe you need a set of parenthesis around the old conditions >>because of the or. >> >> >> >>>>Try: >>>>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 NOT EXISTS ( select t.transportid >>>> from transportsetup t,servicedetail s,logisticservice l >>>> where ts.transportid = t.transportid >>>> >>>> >> >> >> >>>> and t.bizid=l.bizid >>>> >>>> >>Replace the above with: >> and (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' ) >>>> >>>> >> >>Add: >> ) >> >> >> >>>>order by ts.transporttype; >>>> >>>> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 7: don't forget to increase your free space map settings >> >> >> >> > --------------060402000304010603040002 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Thanks Chris and Stephan
the query is working now.

Abdul Wahab Dahalan wrote:
I cant find where to put the parenthesis as u said Stephan.

Here the query :

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 NOT EXISTS (select t.transportid
from transportsetup t,servicedetail s,logisticservice l
where ts.transportid = t.transportid
and 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;



Stephan Szabo wrote:
On Thu, 14 Aug 2003, Abdul Wahab Dahalan wrote:

  
Hai Chris!
Thanks for the solution but seem it doesnt work.
(0 rows) returned when I used NOT EXITS but (4 rows) returned
when NOT IN is used...................
    

Maybe you need a set of parenthesis around the old conditions
because of the or.

  
Try:
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     NOT EXISTS (    select t.transportid
       from transportsetup t,servicedetail s,logisticservice l
       where ts.transportid = t.transportid
      

  
           and t.bizid=l.bizid
      
Replace the above with:
	       and (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' )
      

Add:
		)

  
order by ts.transporttype;
      


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings


--------------060402000304010603040002--