Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id EC9B61F6070B
	for <pgsql-performance@postgresql.org>;
	Fri,  9 Dec 2011 11:30:33 -0400 (AST)
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1RZ2PA-0005bF-Uu
	for pgsql-performance@postgresql.org; Fri, 09 Dec 2011 15:30:33 +0000
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id pB9FUHa2027473;
	Fri, 9 Dec 2011 10:30:17 -0500 (EST)
To: kaloyan@digsys.bg
cc: pgsql-performance@postgresql.org
Subject: Re: Slow query after upgrade from 8.2 to 8.4 
In-reply-to: <4EE1FDC7.70601@digsys.bg> 
References: <4EE0F3F4.8040909@digsys.bg> <14557.1323401295@sss.pgh.pa.us>
	<4EE1FDC7.70601@digsys.bg>
Comments: In-reply-to Kaloyan Iliev Iliev <kaloyan@digsys.bg>
	message dated "Fri, 09 Dec 2011 14:23:35 +0200"
Date: Fri, 09 Dec 2011 10:30:17 -0500
Message-ID: <27472.1323444617@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Pg-Spam-Score: -3.1 (---)
X-Archive-Number: 201112/79
X-Sequence-Number: 45878

Kaloyan Iliev Iliev <kaloyan@digsys.bg> writes:
> <!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>

Please don't post HTML mail.

I think the real issue is that you've got an astonishingly expensive
approach to keeping obsolete "config" rows around.  You should get rid
of that "ORDER BY archived_at" sub-select, either by not storing
obsolete rows at all (you could move them to a history table instead),
or by marking valid rows with a boolean flag.

However, it's not apparent to me why you would see any difference
between 8.2 and 8.4 on this type of query.  I tried a query analogous
to this one on both, and got identical plans.  I'm guessing that your
slowdown is due to not having updated statistics on the new
installation, or perhaps failing to duplicate some relevant settings.

			regards, tom lane