From: Kaloyan Iliev Iliev <kaloyan@digsys.bg>
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query after upgrade from 8.2 to 8.4
Date: Wed, 14 Dec 2011 19:48:17 +0200
Message-ID: <4EE8E161.1090702@digsys.bg> (raw)
In-Reply-To: <4EE7CDEF.1040709@catalyst.net.nz>
References: <4EE0F3F4.8040909@digsys.bg>
<14557.1323401295@sss.pgh.pa.us>
<4EE1FDC7.70601@digsys.bg>
<27472.1323444617@sss.pgh.pa.us>
<4EE7CDEF.1040709@catalyst.net.nz>
Hi,
Thanks for Replay. Actually I finally find a solution. If I rewrite the
query in this way:
explain analyze select 1
from acc_clients AC,
acc_debts AD,
debts_desc DD,
config CF
where AC.ino = 204627 AND
CF.id = (select id
from config
where
confid=CF.confid AND office = 18 ORDER BY archived_at DESC LIMIT 1) AND
AD.transact_no =
AC.transact_no AND
AD.debtid = DD.debtid AND
DD.refid = CF.confid LIMIT 1;
the plan and execution time really approves.
http://explain.depesz.com/s/Nkj
And for comparison I will repost the old way the query was written.
explain analyze select 1
from acc_clients AC,
acc_debts AD,
debts_desc DD,
config CF
where AC.ino = 1200000 AND
CF.id = (select id
from config
where
confid=CF.confid ORDER BY archived_at DESC LIMIT 1) AND
AD.transact_no =
AC.transact_no AND
AD.debtid = DD.debtid AND
CF.office = 18 AND
DD.refid = CF.confid LIMIT 1;
This is the query plan of the upper query.
http://explain.depesz.com/s/ATN
When we have 8.4.9 installed I will try the query and post the result.
Best regards,
Kaloyan Iliev
Mark Kirkwood wrote:
> On 10/12/11 04:30, Tom Lane wrote:
>> 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.
>
> I notice he has 8.4.*8*... I wonder if he's running into the poor
> estimation bug for sub-selects/semi joins that was fixed in 8.4.9.
>
> Kaloyan, can you try the query in 8.4.9?
>
> regards
>
> Mark
>
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, mark.kirkwood@catalyst.net.nz
Subject: Re: Slow query after upgrade from 8.2 to 8.4
In-Reply-To: <4EE8E161.1090702@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