Re: Slow query after upgrade from 8.2 to 8.4

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,
Actually I think the problem is with this sub query:
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;


Instead of starting from 'AC.ino = 1200000' and  limit the rows IT start with 'CF.office = 18' which returns much more rows:
SO: This is the query plan of the upper query.

http://explain.depesz.com/s/ATN


If I remove the condition
'CF.office = 18' the planner chose the correct plan and result is fast.
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
                                             DD.refid = CF.confid LIMIT 1;

http://explain.depesz.com/s/4zb


I want this plan and this query but with the additional condition 'CF.office = 18'.
How could I force the planner to use this plan and just filter the result.

Best regards,
 Kaloyan Iliev


Tom Lane wrote:
Kaloyan Iliev Iliev <kaloyan@xxxxxxxxx> writes:
  
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:
http://explain.depesz.com/s/J0O
    
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

  

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux