From: Antoine Bajolet <antoine.bajolet@free.fr>
To: pgsql-performance@postgresql.org
Subject: Re: Nested Loop trouble : Execution time increases more
Date: Thu, 22 Sep 2005 19:52:54 +0200
Message-ID: <4332EF76.1060101@free.fr> (raw)
In-Reply-To: <4332E604.9020709@free.fr>
References: <432C3A86.5010000@free.fr>
<29637.1127405852@sss.pgh.pa.us>
<4332E604.9020709@free.fr>
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.
>
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: antoine.bajolet@free.fr
Subject: Re: Nested Loop trouble : Execution time increases more
In-Reply-To: <4332EF76.1060101@free.fr>
* 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