From: Simon Riggs <simon@2ndquadrant.com>
To: Antoine Bajolet <antoine.bajolet@free.fr>
Cc: pgsql-performance@postgresql.org
Subject: Re: Nested Loop trouble : Execution time increases more
Date: Thu, 22 Sep 2005 09:24:10 +0100
Message-ID: <1127377450.4145.68.camel@localhost.localdomain> (raw)
In-Reply-To: <432C3A86.5010000@free.fr>
References: <432C3A86.5010000@free.fr>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: simon@2ndquadrant.com, antoine.bajolet@free.fr
Subject: Re: Nested Loop trouble : Execution time increases more
In-Reply-To: <1127377450.4145.68.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox