Re: Very ineffective plan with merge join

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.

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.
        regards, tom lane