X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3048A329C6B for ; Thu, 9 Sep 2004 15:56:15 +0100 (BST) 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 64297-09 for ; Thu, 9 Sep 2004 14:56:07 +0000 (GMT) Received: from mailer.elma.loc (mail.elma.fr [213.41.14.138]) by svr1.postgresql.org (Postfix) with ESMTP id 1DA87329C67 for ; Thu, 9 Sep 2004 15:56:09 +0100 (BST) Received: from mailer.elma.loc (localhost [127.0.0.1]) by localhost (Postfix) with ESMTP id 640EDEC16C; Thu, 9 Sep 2004 15:57:10 +0200 (CEST) Received: from zoot.elma.fr (herve.elma.fr [10.0.1.2]) by mailer.elma.loc (Postfix) with ESMTP id BC7B1EC267; Thu, 9 Sep 2004 15:57:05 +0200 (CEST) From: =?iso-8859-15?q?Herv=E9_Piedvache?= Organization: Elma =?iso-8859-15?q?Ing=E9nierie?= Informatique To: George Essig Subject: Re: TSearch2 and optimisation ... Date: Thu, 9 Sep 2004 16:56:01 +0200 User-Agent: KMail/1.7 Cc: pgsql-performance@postgresql.org, footcow@noos.fr References: <20040826175833.32182.qmail@web53703.mail.yahoo.com> In-Reply-To: <20040826175833.32182.qmail@web53703.mail.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: 8bit Content-Disposition: inline Message-Id: <200409091656.02006.herve@elma.fr> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/54 X-Sequence-Number: 8182 George, Le Jeudi 26 Août 2004 19:58, George Essig a écrit : > Bill Footcow wrote: > > ... > > > I have done a simple request, looking for title or description having > > Postgres inside order by rank and date, like this : > > SELECT a.title, a.id, a.url, to_char(a.r_date, 'DD/MM/YYYY HH24:MI:SS') > > as dt, s.site_name, s.id_site, case when exists (select id_user from > > user_choice u where u.id_site=s.id_site and u.id_user = 1) then 1 else 0 > > end as bookmarked FROM article a, site s > > WHERE s.id_site = a.id_site > > AND idxfti @@ to_tsquery('postgresql') > > ORDER BY rank(idxfti, to_tsquery('postgresql')) DESC, a.r_date DESC; > > > > The request takes about 4 seconds ... I have about 1 400 000 records in > > article and 36 000 records in site table ... it's a Bi-Pentium III 933 > > MHz server with 1 Gb memory ... I'm using Postgresql 7.4.5 > > For me this result is very very slow I really need a quicker result with > > less than 1 second ... > > The next time I call the same request I have got the result in 439 ms ... > > but > > ... > > The first query is slow because the relevant index pages are not cached in > memory. Everyone experiences this. GiST indexes on tsvector columns can > get really big. You have done nothing wrong. When you have a lot of > records, tsearch2 will not run fast without extensive performance tuning. > > Read the following: > > Optimization > http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/oscon_tsearch2/ >optimization.html > > ... I have well read many pages about this subject ... but I have not found any thing for the moment to really help me ... What can I do to optimize my PostgreSQL configuration for a special use of Tsearch2 ... I'm a little dispointed looking the Postgresql Russian search engine using Tsearch2 is really quick ... why I can't haev the same result with a bi-pentium III 933 and 1Gb of RAM with the text indexation of 1 500 000 records ? Regards, -- Hervé Piedvache Elma Ingénierie Informatique 6 rue du Faubourg Saint-Honoré F-75008 - Paris - France Pho. 33-144949901 Fax. 33-144949902