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: 603c8f070902100506w7074ee35qb22f701cf751c7f0@mail.gmail.com (view raw or flat)
Thread:
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.

...Robert

In response to

Responses

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/Dbt2.pm line 521.

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