Re: Slow query after upgrade from 8.2 to 8.4

From: Kaloyan Iliev Iliev <kaloyan(at)digsys(dot)bg>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow query after upgrade from 8.2 to 8.4
Date: 2011-12-09 12:23:35
Message-ID: 4EE1FDC7.70601@digsys.bg
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=windows-1251"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<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>
                                            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>
                                              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
start with '</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 select 1<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 force the 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(dot)1323401295(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<pre wrap="">Kaloyan Iliev Iliev <a class="moz-txt-link-rfc2396E" href="mailto:kaloyan(at)digsys(dot)bg">&lt;kaloyan(at)digsys(dot)bg&gt;</a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">We recently upgrade 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>
<pre wrap=""><!---->
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>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 3.7 KB

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2011-12-09 15:30:17 Re: Slow query after upgrade from 8.2 to 8.4
Previous Message Tom Lane 2011-12-09 03:28:15 Re: Slow query after upgrade from 8.2 to 8.4