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 06CD4D1C943 for ; Thu, 14 Aug 2003 03:22:37 +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 43054-05 for ; Thu, 14 Aug 2003 00:20:58 -0300 (ADT) Received: from filter.mimos.my (filter.mimos.my [192.228.137.70]) by svr1.postgresql.org (Postfix) with ESMTP id D0F2DD1C50D for ; Thu, 14 Aug 2003 00:22:24 -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 h7E2kxr13499; Thu, 14 Aug 2003 10:46:59 +0800 Received: (from root@localhost) by ew.mimos.my (8.12.3/8.12.3) id h7E3MKNR068706; Thu, 14 Aug 2003 11:22:20 +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 h7E3MHtv068335; Thu, 14 Aug 2003 11:22:19 +0800 (MYT) (envelope-from wahab@mimos.my) Message-ID: <3F3B006D.4000708@mimos.my> Date: Thu, 14 Aug 2003 11:22: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: Stephan Szabo Cc: Christoph Haller , pgsql-sql@postgresql.org Subject: Re: How to speeed up the query performance References: <20030813201031.D67777-100000@megazone.bigpanda.com> X-scanner: scanned by Inflex 1.0.10 - (http://pldaniels.com/inflex/) Content-Type: multipart/alternative; boundary="------------040500040300090704030504" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/166 X-Sequence-Number: 14782 --------------040500040300090704030504 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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 > > > > --------------040500040300090704030504 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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

--------------040500040300090704030504--