Re: Very ineffective plan with merge join

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 <oleg(at)sai(dot)msu(dot)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: oleg(at)sai(dot)msu(dot)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: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83