From: Tom Lane <tgl@sss.pgh.pa.us>
To: Antoine Bajolet <antoine.bajolet@free.fr>
Cc: pgsql-performance@postgresql.org
Subject: Re: Nested Loop trouble : Execution time increases more 1000 time (long)
Date: Thu, 22 Sep 2005 12:17:32 -0400
Message-ID: <29637.1127405852@sss.pgh.pa.us> (raw)
In-Reply-To: <432C3A86.5010000@free.fr>
References: <432C3A86.5010000@free.fr>
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:
> -> 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?
regards, tom lane
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: tgl@sss.pgh.pa.us, antoine.bajolet@free.fr
Subject: Re: Nested Loop trouble : Execution time increases more 1000 time (long)
In-Reply-To: <29637.1127405852@sss.pgh.pa.us>
* 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