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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
