Re: query becomes fas on 'SET enable_hashjoin TO off;'

Rajesh Kumar Mallah <mallah(dot)rajesh(at)gmail(dot)com> writes:
> On Tue, Feb 10, 2009 at 6:36 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> I'm guessing that the problem is that the selectivity estimate for
>> co_name_vec @@ to_tsquery('plastic&tubes') is not very good, but I'm
>> not real familiar with full text search, so I'm not sure whether
>> there's anything sensible you can do about it.

Yeah, the bad selectivity estimate seems to be the entire problem ---
if that were even slightly closer to reality the planner would've
preferred the nestloop.

I don't think there's a good solution to this in 8.3, because its
estimator for @@ is just a stub. There will be a non-toy estimator
in 8.4, fwiw.

A possibility that seems a bit less crude than turning off hashjoins
is to reduce random_page_cost, so as to bias things toward nestloop
indexscans in general.

regards, tom lane