X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 3AEDEDAAAB
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Sep 2005 14:53:10 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 86496-08
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Sep 2005 17:53:07 +0000 (GMT)
Received: from pentium2.antoine (m119.net81-67-244.noos.fr [81.67.244.119])
	by svr1.postgresql.org (Postfix) with ESMTP id 9B27FDAAC1
	for <pgsql-performance@postgresql.org>;
	Thu, 22 Sep 2005 14:53:06 -0300 (ADT)
Received: from [192.168.1.11] (pentium4.antoine [192.168.1.11])
	by pentium2.antoine (Postfix) with ESMTP id 50B34C7A49
	for <pgsql-performance@postgresql.org>;
	Thu, 22 Sep 2005 19:52:54 +0200 (CEST)
Message-ID: <4332EF76.1060101@free.fr>
Date: Thu, 22 Sep 2005 19:52:54 +0200
From: Antoine Bajolet <antoine.bajolet@free.fr>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; fr-FR;
	rv:1.7.11) Gecko/20050728
X-Accept-Language: fr, en, en-us
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Nested Loop trouble : Execution time increases more
References: <432C3A86.5010000@free.fr> <29637.1127405852@sss.pgh.pa.us>
	<4332E604.9020709@free.fr>
In-Reply-To: <4332E604.9020709@free.fr>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.394 required=5 tests=[AWL=-0.448,
	FORGED_RCVD_HELO=0.05, RCVD_IN_NJABL_DUL=1.655, RCVD_IN_SORBS_DUL=0.137]
X-Spam-Level: *
X-Archive-Number: 200509/245
X-Sequence-Number: 14553

Re,

With modifing parameters like this :

ALTER TABLE keywords ALTER keyword SET STATISTICS 100;
ALTER TABLE keywords ALTER k_id SET STATISTICS 100;
ALTER TABLE engine ALTER k_id SET STATISTICS 100;
ALTER TABLE engine ALTER f_id SET STATISTICS 100;

vacuuming both tables
and rewriting the queries using sub-selects :

select count (distinct f.f_id) as results
FROM
fiches f
INNER JOIN (SELECT distinct f_id FROM keywords,engine WHERE engine.k_id 
= keywords.k_id AND keyword like 'exploitation%') as e1 USING(f_id)
INNER JOIN (SELECT distinct f_id FROM keywords,engine WHERE engine.k_id 
= keywords.k_id AND keyword like 'maintenance%') as e2 USING(f_id)
INNER JOIN (SELECT distinct f_id FROM keywords,engine WHERE engine.k_id 
= keywords.k_id AND keyword like 'numerique%') as e3 USING(f_id)

The query time is less than 600 ms, and increases only a little adding 
more keywords.

Thanks to Tom Lane and Simon Riggs.

Best regards,
Antoine Bajolet

Antoine Bajolet a écrit :

> Hello,
>
> Tom Lane a écrit :
>
>> Antoine Bajolet <antoine.bajolet@free.fr> writes:
>>  
>>
>>> We are using postgresql in a search engine on an intranet handling 
>>> throusand of documents.
>>> But we ave a big problem when users use more than two search key.
>>>   
>>
>>
>> I think you need to increase the statistics targets for your keywords
>> table --- the estimates of numbers of matching rows are much too small:
>>  
>>
> What value you think i could put into a ALTER TABLE SET STATISTICS 
> statment ?
>
> Also, the solution given by Simon Riggs works well.
> <quote>
>
> Recode your SQL with an IN subselect that retrieves all possible 
> keywords before it accesses the larger table.
> </quote>
>
> But i will try the old ones increasing the statistics parameter and 
> compare performance.
>