Sorry, I used random_page_cost=2, while random_page_cost=3 didn't help.
Oleg
On Fri, 16 Apr 2010, Oleg Bartunov wrote:
On Thu, 15 Apr 2010, Tom Lane wrote:
Oleg Bartunov <o...@sai.msu.su> writes:
below is an example of interesting query and two plans - the bad plan,
which
uses merge join and big sorting, took 216 sec, and good plan with merge
join disabled took
8 sec.
The "good" plan seems to be fast mainly because of heavily cached inner
indexscans. If that's the normal operating state for this database, you
should try reducing random_page_cost.
Hmm, reducing random_page_cost to 3 helps, now all plans are the same.
Also, as Pavel noted, the sub-join size estimates aren't very good, and
those overestimates are discouraging it from using inner-indexscan
nestloops. I'm not sure how much it would help to increase the
statistics targets, but that would be worth trying.
Yes, setting statistics to 1000 helped for that paticular query (reduced by
me),
but full query still chooses wrong plan with merge join. As you say before,
random_page_cost=3 helped.
I'm wondering if postgres could recognize such case (heavily cached inner
indexscans).
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
