Skip site navigation (1) Skip section navigation (2)

Re: Slow query after upgrade from 8.2 to 8.4

From: Kaloyan Iliev Iliev <kaloyan(at)digsys(dot)bg>
To: Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow query after upgrade from 8.2 to 8.4
Date: 2011-12-14 17:48:17
Message-ID: 4EE8E161.1090702@digsys.bg (view raw or flat)
Thread:
Lists: pgsql-performance
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
>

In response to

pgsql-performance by date

Next:From: Marti RaudseppDate: 2011-12-14 20:43:37
Subject: Re: Is it possible to use index on column for regexp match operator '~'?
Previous:From: voodoolessDate: 2011-12-14 16:06:20
Subject: Re: Partitions and joins lead to index lookups on all partitions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group