|
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/J0OYeah, 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 |
