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 ECFD2DAA11
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Sep 2005 14:12:54 -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 66697-06
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Sep 2005 17:12:52 +0000 (GMT)
Received: from pentium2.antoine (m119.net81-67-244.noos.fr [81.67.244.119])
	by svr1.postgresql.org (Postfix) with ESMTP id 20A22DAA51
	for <pgsql-performance@postgresql.org>;
	Thu, 22 Sep 2005 14:12:50 -0300 (ADT)
Received: from [192.168.1.11] (pentium4.antoine [192.168.1.11])
	by pentium2.antoine (Postfix) with ESMTP
	id 0C3D8C7A49; Thu, 22 Sep 2005 19:12:37 +0200 (CEST)
Message-ID: <4332E604.9020709@free.fr>
Date: Thu, 22 Sep 2005 19:12:36 +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
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Nested Loop trouble : Execution time increases more
References: <432C3A86.5010000@free.fr> <29637.1127405852@sss.pgh.pa.us>
In-Reply-To: <29637.1127405852@sss.pgh.pa.us>
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/244
X-Sequence-Number: 14552

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.


>  
>
>>                    ->  Index Scan using keyword_pattern_key on keywords 
>>k2  (cost=0.00..3.51 rows=1 width=4) (actual time=0.078..1.887 rows=75 
>>loops=1)
>>                          Index Cond: (((keyword)::text ~>=~ 
>>'exploitation'::character varying) AND ((keyword)::text ~<~ 
>>'exploitatioo'::character varying))
>>                          Filter: ((keyword)::text ~~ 'exploitation%'::text)
>>    
>>
>
>A factor-of-75 error is quite likely to mislead the planner into
>choosing a bad join plan.
>
>BTW, have you looked into using a real full-text-search engine (eg,
>tsearch2) instead of rolling your own like this?
>  
>
It seems a quite good contrib, but...
The first version of this search engine was developped in 2000... 
tsearch2 nor tsearch existed at this time.
Also, there are some developpement works around this search engine 
(pertinence algorithm, filtering with users rights, ponderating keywords 
with specific rules to each type of document, etc.) and adapting all to 
work in the similar way with tsearch2 seems to be a bit heavy.
At the end, each document indexed are quite big and the choosen method 
reduces disk storage : 1 Go of text content traduces to ~100 Mo of table 
space.

Best Regards,
Antoine Bajolet