From: Abdul Wahab Dahalan <wahab@mimos.my>
To: Abdul Wahab Dahalan <wahab@mimos.my>
Cc: 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:35:21 +0800
Message-ID: <3F3B0379.9030602@mimos.my> (raw)
References: <20030813201031.D67777-100000@megazone.bigpanda.com>
<3F3B006D.4000708@mimos.my>
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
>>
>>
>>
>>
>
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: <3F3B0379.9030602@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