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