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

On Tue, Feb 10, 2009 at 5:31 AM, Rajesh Kumar Mallah
<mallah(dot)rajesh(at)gmail(dot)com> wrote:
> I have a query in which two huge tables (A,B) are joined using an indexed
> column and a search is made on tsvector on some column on B. Very limited
> rows of B are expected to match the query on tsvector column.
>
> With default planner settings the query takes too long ( > 100 secs) , but
> with hashjoin off it returns almost immediately. The question is , is
> it is advisable to
> tweak planner settings for specific queries in application ?

The ones that start with "enable_" usually shouldn't be changed.
They're mostly for debugging and finding problems.

> The plans are as follows.

It's a little hard to figure out what's gone wrong here because you've
only included EXPLAIN ANALYZE output for one of the plans - the other
is just regular EXPLAIN. Can you send that, along with the output of
the following query:

SELECT SUM(1) FROM trade_leads WHERE status = 'm'

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.

...Robert