Re: How to speeed up the query performance - Mailing list pgsql-sql

Hai Chris!<br /> Thanks for the solution but seem it doesnt work.<br /> (0 rows) returned when I used NOT EXITS but (4
rows)returned<br /> when NOT IN is used...................<br /><br /> FYI I used 7.2<br /><br /> Christoph Haller
wrote:<br/><blockquote cite="mid3F3A501A.FDF96A90@rodos.fzk.de" type="cite"><blockquote type="cite"><pre wrap="">How do
Ispeed up the quey performance if I've a query like this :
 
Does 'not in' command will affected the performance?.   </pre></blockquote><pre wrap="">Yes, it's well known to be slow
in7.3 and lower, should be fixed in 7.4
 
AFAIK. </pre><blockquote type="cite"><pre wrap="">select
   </pre></blockquote><pre
wrap="">ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber
 </pre><blockquote type="cite"><pre wrap="">from       transportsetup ts
where     ts.bizid = 'B126'
and         ts.deletestatus = 0
and         ts.transportid not in (    select t.transportid                                                 from
transportsetup  </pre></blockquote><pre wrap="">t,servicedetail s,logisticservice l </pre><blockquote type="cite"><pre
wrap="">                                                 where   </pre></blockquote><pre wrap="">t.bizid=l.bizid
</pre><blockquotetype="cite"><pre wrap="">                                                      and
</pre></blockquote><prewrap="">l.serviceid=s.serviceid </pre><blockquote type="cite"><pre wrap="">
                               and   </pre></blockquote><pre wrap="">t.transportid=s.transportid </pre><blockquote
type="cite"><prewrap="">                                                      and t.bizid =   </pre></blockquote><pre
wrap="">'B126'</pre><blockquote type="cite"><pre wrap="">                                                      and
</pre></blockquote><prewrap="">l.status='Pending' </pre><blockquote type="cite"><pre wrap="">
                      or t.bizid=l.bizid                                                     and
</pre></blockquote><prewrap="">l.serviceid=s.serviceid </pre><blockquote type="cite"><pre wrap="">
                               and   </pre></blockquote><pre wrap="">t.transportid=s.transportid </pre><blockquote
type="cite"><prewrap="">                                                      and t.bizid =   </pre></blockquote><pre
wrap="">'B126'and l.status='Reserved' ) </pre><blockquote type="cite"><pre wrap="">order by ts.transporttype;
 
   </pre></blockquote><pre wrap="">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


 </pre></blockquote><br />