Re: Nested Loop trouble : Execution time increases more

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.
>

In response to

Browse pgsql-performance by date

  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