From: | Antoine Bajolet <antoine(dot)bajolet(at)free(dot)fr> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Nested Loop trouble : Execution time increases more |
Date: | 2005-09-22 17:52:54 |
Message-ID: | 4332EF76.1060101@free.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)bajolet(at)free(dot)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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2005-09-22 18:07:38 | Re: SELECT LIMIT 1 VIEW Performance Issue |
Previous Message | Antoine Bajolet | 2005-09-22 17:12:36 | Re: Nested Loop trouble : Execution time increases more |