pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to speeed up the query performance
7+ messages / 4 participants
[nested] [flat]
* How to speeed up the query performance
@ 2003-08-13 03:20 Abdul Wahab Dahalan <wahab@mimos.my>
2003-08-13 14:50 ` Re: How to speeed up the query performance Jonathan Gardner <jgardner@jonathangardner.net>
0 siblings, 1 reply; 7+ messages in thread
From: Abdul Wahab Dahalan @ 2003-08-13 03:20 UTC (permalink / raw)
To: pgsql-sql; wahab@mimos.my
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to speeed up the query performance
2003-08-13 03:20 How to speeed up the query performance Abdul Wahab Dahalan <wahab@mimos.my>
@ 2003-08-13 14:50 ` Jonathan Gardner <jgardner@jonathangardner.net>
0 siblings, 0 replies; 7+ messages in thread
From: Jonathan Gardner @ 2003-08-13 14:50 UTC (permalink / raw)
To: Abdul Wahab Dahalan <wahab@mimos.my>; pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Tuesday 12 August 2003 20:20, Abdul Wahab Dahalan wrote:
> How do I speed up the quey performance if I've a query like this :
> Does 'not in' command will affected the performance?.
>
Yes. Severely. See the responses to the "How to optimize this query ?"
thread.
If you want more details, check the pgsql-performance archives.
- --
Jonathan Gardner <jgardner@jonathangardner.net>
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/OlBBWgwF3QvpWNwRAuzJAJ99iMmMbU/tiJhi077+8WCmAId76ACffL+5
biOZSLPbuhWZBL6MNlZE3V0=
=Sg0n
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to speeed up the query performance
@ 2003-08-13 14:50 Christoph Haller <ch@rodos.fzk.de>
2003-08-14 02:51 ` Re: How to speeed up the query performance Abdul Wahab Dahalan <wahab@mimos.my>
0 siblings, 1 reply; 7+ messages in thread
From: Christoph Haller @ 2003-08-13 14:50 UTC (permalink / raw)
To: pgsql-sql; wahab@mimos.my
>
> How do I speed up the quey performance if I've a query like this :
> Does 'not in' command will affected the performance?.
Yes, it's well known to be slow in 7.3 and lower, should be fixed in 7.4
AFAIK.
>
> 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
> from 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;
>
As recently mentioned by Stephan Szabo on '[SQL] How to optimize this
query ?'
NOT EXISTS performs much better.
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
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;
Regards, Christoph
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to speeed up the query performance
2003-08-13 14:50 Re: How to speeed up the query performance Christoph Haller <ch@rodos.fzk.de>
@ 2003-08-14 02:51 ` Abdul Wahab Dahalan <wahab@mimos.my>
2003-08-14 03:12 ` Re: How to speeed up the query performance Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 7+ messages in thread
From: Abdul Wahab Dahalan @ 2003-08-14 02:51 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
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...................
FYI I used 7.2
Christoph Haller wrote:
>>How do I speed up the quey performance if I've a query like this :
>>Does 'not in' command will affected the performance?.
>>
>>
>Yes, it's well known to be slow in 7.3 and lower, should be fixed in 7.4
>AFAIK.
>
>
>>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
>> from 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;
>>
>>
>>
>As recently mentioned by Stephan Szabo on '[SQL] How to optimize this
>query ?'
>NOT EXISTS performs much better.
>
>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
> 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;
>
>Regards, Christoph
>
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to speeed up the query performance
2003-08-13 14:50 Re: How to speeed up the query performance Christoph Haller <ch@rodos.fzk.de>
2003-08-14 02:51 ` Re: How to speeed up the query performance Abdul Wahab Dahalan <wahab@mimos.my>
@ 2003-08-14 03:12 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-14 03:22 ` Re: How to speeed up the query performance Abdul Wahab Dahalan <wahab@mimos.my>
0 siblings, 1 reply; 7+ messages in thread
From: Stephan Szabo @ 2003-08-14 03:12 UTC (permalink / raw)
To: Abdul Wahab Dahalan <wahab@mimos.my>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
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;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to speeed up the query performance
2003-08-13 14:50 Re: How to speeed up the query performance Christoph Haller <ch@rodos.fzk.de>
2003-08-14 02:51 ` Re: How to speeed up the query performance Abdul Wahab Dahalan <wahab@mimos.my>
2003-08-14 03:12 ` Re: How to speeed up the query performance Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-08-14 03:22 ` Abdul Wahab Dahalan <wahab@mimos.my>
2003-08-14 03:35 ` Re: How to speeed up the query performance Abdul Wahab Dahalan <wahab@mimos.my>
0 siblings, 1 reply; 7+ messages in thread
From: Abdul Wahab Dahalan @ 2003-08-14 03:22 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
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
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to speeed up the query performance
2003-08-13 14:50 Re: How to speeed up the query performance Christoph Haller <ch@rodos.fzk.de>
2003-08-14 02:51 ` Re: How to speeed up the query performance Abdul Wahab Dahalan <wahab@mimos.my>
2003-08-14 03:12 ` Re: How to speeed up the query performance Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-14 03:22 ` Re: How to speeed up the query performance Abdul Wahab Dahalan <wahab@mimos.my>
@ 2003-08-14 03:35 ` Abdul Wahab Dahalan <wahab@mimos.my>
0 siblings, 0 replies; 7+ messages in thread
From: Abdul Wahab Dahalan @ 2003-08-14 03:35 UTC (permalink / raw)
To: Abdul Wahab Dahalan <wahab@mimos.my>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
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
>>
>>
>>
>>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-08-14 03:35 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-13 03:20 How to speeed up the query performance Abdul Wahab Dahalan <wahab@mimos.my>
2003-08-13 14:50 ` Jonathan Gardner <jgardner@jonathangardner.net>
2003-08-13 14:50 Re: How to speeed up the query performance Christoph Haller <ch@rodos.fzk.de>
2003-08-14 02:51 ` Abdul Wahab Dahalan <wahab@mimos.my>
2003-08-14 03:12 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-14 03:22 ` Abdul Wahab Dahalan <wahab@mimos.my>
2003-08-14 03:35 ` Abdul Wahab Dahalan <wahab@mimos.my>
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