From: Abdul Wahab Dahalan <wahab@mimos.my>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Christoph Haller <ch@rodos.fzk.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to speeed up the query performance
Date: Thu, 14 Aug 2003 11:22:21 +0800
Message-ID: <3F3B006D.4000708@mimos.my> (raw)
References: <20030813201031.D67777-100000@megazone.bigpanda.com>
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
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: wahab@mimos.my, sszabo@megazone.bigpanda.com, ch@rodos.fzk.de
Subject: Re: How to speeed up the query performance
In-Reply-To: <3F3B006D.4000708@mimos.my>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox