Re: Slow query after upgrade from 8.2 to 8.4 - Mailing list pgsql-performance

<tt>Hi,<br /> Actually I think the problem is with this sub query:<br /> explain analyze select 1<br />
                                      from acc_clients AC,<br />                                             acc_debts
AD,<br/>                                             debts_desc DD,<br />                                            
configCF<br />                                        where AC.ino = 1200000 AND<br /><br />
                                            CF.id = (select id<br />
                                                      from config<br />
                                                      where confid=CF.confid ORDER BY archived_at DESC LIMIT 1) AND<br
/>                                             AD.transact_no = AC.transact_no AND<br />
                                            AD.debtid = DD.debtid AND<br />
                                             CF.office = 18 AND<br />                                             
DD.refid= CF.confid LIMIT 1;</tt><br /><br /> Instead of starting from '<tt>AC.ino = 1200000' and  limit the rows IT
startwith '</tt><tt>CF.office = 18' which returns much more rows:<br /> SO: This is the query plan of the upper
query.<br/><br /><a class="moz-txt-link-freetext"
href="http://explain.depesz.com/s/ATN">http://explain.depesz.com/s/ATN</a><br/><br /><br /> If I remove the condition
</tt><tt>'</tt><tt>CF.office= 18' the planner chose the correct plan and result is fast.</tt><br /><tt>explain analyze
select1<br />                                        from acc_clients AC,<br />
                                           acc_debts AD,<br />                                             debts_desc
DD,<br/>                                             config CF<br />                                        where
AC.ino= 1200000 AND<br /><br />                                              CF.id = (select id<br />
                                                      from config<br />
                                                      where confid=CF.confid ORDER BY archived_at DESC LIMIT 1) AND<br
/>                                             AD.transact_no = AC.transact_no AND<br />
                                            AD.debtid = DD.debtid AND<br />
                                            DD.refid = CF.confid LIMIT 1;<br /></tt><tt><br /><a
class="moz-txt-link-freetext"href="http://explain.depesz.com/s/4zb">http://explain.depesz.com/s/4zb</a></tt><br /><br
/>I want this plan and this query but with the additional condition<tt> '</tt><tt>CF.office = 18'.<br /> How could I
forcethe planner to use this plan and just filter the result.<br /><br /> Best regards,<br />  Kaloyan Iliev<br
/></tt><br/><br /> Tom Lane wrote: <blockquote cite="mid:14557.1323401295@sss.pgh.pa.us" type="cite"><pre
wrap="">KaloyanIliev Iliev <a class="moz-txt-link-rfc2396E"
href="mailto:kaloyan@digsys.bg"><kaloyan@digsys.bg></a>writes: </pre><blockquote type="cite"><pre wrap="">We
recentlyupgrade our server from PG8.2 to 8.4.
 
...
Here I will post explain analyze. If you think it is necessary I will 
post the exact query:
<a class="moz-txt-link-freetext" href="http://explain.depesz.com/s/J0O">http://explain.depesz.com/s/J0O</a>
</pre></blockquote><prewrap="">
 
Yeah, you need to show the query.  It looks like the performance problem
is stemming from a lot of subselects, but it's not clear why 8.4 would
be handling those worse than 8.2.
        regards, tom lane
 </pre></blockquote>