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