Skip site navigation (1) Skip section navigation (2)

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

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Rajesh Kumar Mallah <mallah(dot)rajesh(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: query becomes fas on 'SET enable_hashjoin TO off;'
Date: 2009-02-10 13:06:30
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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.


In response to


pgsql-performance by date

Next:From: Rajesh Kumar MallahDate: 2009-02-10 14:29:42
Subject: Re: query becomes fas on 'SET enable_hashjoin TO off;'
Previous:From: Rohan PethkarDate: 2009-02-10 12:46:19
Subject: ERROR: Can't use an undefined value as an ARRAY reference at /usr/lib/perl5/site_perl/5.8.8/Test/Parser/ line 521.

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group