From: Tom Lane <tgl@sss.pgh.pa.us>
To: kaloyan@digsys.bg
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query after upgrade from 8.2 to 8.4
Date: Fri, 09 Dec 2011 10:30:17 -0500
Message-ID: <27472.1323444617@sss.pgh.pa.us> (raw)
In-Reply-To: <4EE1FDC7.70601@digsys.bg>
References: <4EE0F3F4.8040909@digsys.bg>
<14557.1323401295@sss.pgh.pa.us>
<4EE1FDC7.70601@digsys.bg>
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
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: tgl@sss.pgh.pa.us, kaloyan@digsys.bg
Subject: Re: Slow query after upgrade from 8.2 to 8.4
In-Reply-To: <27472.1323444617@sss.pgh.pa.us>
* 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