pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Kaloyan Iliev Iliev <kaloyan@digsys.bg>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query after upgrade from 8.2 to 8.4
Date: Fri, 09 Dec 2011 14:23:35 +0200
Message-ID: <4EE1FDC7.70601@digsys.bg> (raw)
In-Reply-To: <14557.1323401295@sss.pgh.pa.us>
References: <4EE0F3F4.8040909@digsys.bg>
	<14557.1323401295@sss.pgh.pa.us>

<!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.1323401295@sss.pgh.pa.us" type="cite">
  <pre wrap="">Kaloyan Iliev Iliev <a class="moz-txt-link-rfc2396E" href="mailto:kaloyan@digsys.bg">&lt;kaloyan@digsys.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>


view thread (7+ messages)  latest in thread

Message-ID: <4EE1FDC7.70601@digsys.bg>
Permalink:  ../4EE1FDC7.70601@digsys.bg/
Also on:    postgresql.org/message-id/4EE1FDC7.70601@digsys.bg

 · 

reply
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-performance@postgresql.org
  Cc: kaloyan@digsys.bg, tgl@sss.pgh.pa.us
  Subject: Re: Slow query after upgrade from 8.2 to 8.4
  In-Reply-To: <4EE1FDC7.70601@digsys.bg>

* 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