Subject: Re: Nested Loop trouble : Execution time increases more
From: Simon Riggs <simon@2ndquadrant.com>
To: Antoine Bajolet <antoine.bajolet@free.fr>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <432C3A86.5010000@free.fr>
References: <432C3A86.5010000@free.fr>
Content-Type: text/plain
Organization: 2nd Quadrant
Date: Thu, 22 Sep 2005 09:24:10 +0100
Message-Id: <1127377450.4145.68.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Sat, 2005-09-17 at 17:47 +0200, Antoine Bajolet wrote:

> There are more tables around, but the heart of  the search engine is 
> made of three tables :
> 
> fiches (f_id int4, f_title varchar)          52445 rows
> engine (f_id int4, k_id int4, weight )    11761700 rows
> keywords(k_id, keyword)                    1072600 rows
> 
> A "fiche" is linked to any kind of document.
> The engine table counts how many times a keyword appears in a document.
> 
> A query to search on one or two keywords is quick to execute (the 
> front-end creates thoses queries):
> 

> Is there a specific reason the planner chooses this way ?

Yes, you have an additional join for each new keyword, so there is more
work to do.

Recode your SQL with an IN subselect that retrieves all possible
keywords before it accesses the larger table.

That way you should have only one join for each new keyword.

> Can whe do something on the postgresql configuration to avoid this ?
> Can whe force the planner to use a hash join as it does for the first 
> joins ?

Not required, IMHO.

Best Regards, Simon Riggs