Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 64CF29FB5B5
	for <pgsql-general-postgresql.org@postgresql.org>;
	Tue, 20 Feb 2007 06:42:00 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 60952-03 for
	<pgsql-general-postgresql.org@postgresql.org>;
	Tue, 20 Feb 2007 06:41:47 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from an-out-0708.google.com (an-out-0708.google.com
 [209.85.132.244])
	by postgresql.org (Postfix) with ESMTP id 972159FB550
	for <pgsql-general@postgresql.org>;
	Tue, 20 Feb 2007 06:41:56 -0400 (AST)
Received: by an-out-0708.google.com with SMTP id c24so340504ana
	for <pgsql-general@postgresql.org>;
	Tue, 20 Feb 2007 02:41:55 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=F/HalpCIYz0gbEzl0DIoblefHXgNkYZ+zg2/cs/hRjDe8HHZkrrl8zehZVEzwA0c7kCe63RmvEIVAXhzJjwEUO0/k/DqM/8xRIPTa4K74s9lSWYaavjDwLCMKehH/+iBEsZWG+yf4Jpu0fSw0EDc7qTrE7TMSqNEdo1TIPpF4+s=
Received: by 10.114.39.16 with SMTP id m16mr3278693wam.1171968115066;
	Tue, 20 Feb 2007 02:41:55 -0800 (PST)
Received: by 10.114.80.16 with HTTP; Tue, 20 Feb 2007 02:41:54 -0800 (PST)
Message-ID: <bd8b58a40702200241y7ad2ae60q4e39bbfcfe1eed2f@mail.gmail.com>
Date: Tue, 20 Feb 2007 11:41:54 +0100
From: "Rafa Comino" <rafacomino@gmail.com>
To: pgsql-general@postgresql.org, pgsql-sql@postgresql.org,
	pgsql-performance@postgresql.org
Subject: Having performance problems with TSearch2
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_46790_6655762.1171968114849"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.375 tagged_above=0 required=5 tests=BAYES_50,
	HTML_30_40, HTML_MESSAGE, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200702/1045
X-Sequence-Number: 109535

------=_Part_46790_6655762.1171968114849
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I have problems with queries over tsearch index.
I have a table of books, with 1200000 registers. I have created an GIST
index over the title and subtitle,

CREATE INDEX "idxts2_titsub_idx" ON "public"."libros" USING gist
("idxts2_titsub");

My problems started when i execute my queries.
For example, i execute a simple query like this one:
explain analyze
        SELECT isbn, titulo
          FROM libros
         WHERE idxts2_titsub @@ to_tsquery('default_spanish',
to_ascii('sevilla'))
         ORDER BY titulo
         LIMIT 10;
This query take more than 10 secods, and i think this is too much for such
an easy query.
Every night, i execute a VACUUM ANALYZE over my data base.

The query plan of this query, is the next one:
QUERY PLAN
Limit  (cost=4725.18..4725.20 rows=10 width=56) (actual time=
17060.826..17061.078 rows=10 loops=1)
  ->  Sort  (cost=4725.18..4728.23 rows=1223 width=56) (actual time=
17060.806..17060.874 rows=10 loops=1)
        Sort Key: titulo
        ->  Bitmap Heap Scan on libros  (cost=45.28..4662.46 rows=1223
width=56) (actual time=10831.530..16957.667 rows=2542 loops=1)
              Filter: (idxts2_titsub @@ '''sevilla'''::tsquery)
              ->  Bitmap Index Scan on idxts2_titsub_idx
(cost=0.00..45.28rows=1223 width=0) (actual time=
10830.051..10830.051 rows=2586 loops=1)
                    Index Cond: (idxts2_titsub @@ '''sevilla'''::tsquery)
Total runtime: 17062.665 ms

I have no idea what is happening. Why the Bitmap Index Scan and the Bitmap
Heap Scan cost so much time?

I have a  2GB RAM memory Server.

Thanks every body for your healp and sorry for my English

------=_Part_46790_6655762.1171968114849
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I have problems with queries over tsearch index.<br>I have a table of books, with 1200000 registers. I have created an GIST index over the title and subtitle, <br><br>CREATE INDEX &quot;idxts2_titsub_idx&quot; ON &quot;public&quot;.&quot;libros&quot; USING gist (&quot;idxts2_titsub&quot;);
<br><br>My problems started when i execute my queries. <br>For example, i execute a simple query like this one:<br>explain analyze<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT isbn, titulo<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM libros<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE idxts2_titsub @@ to_tsquery(&#39;default_spanish&#39;, to_ascii(&#39;sevilla&#39;))
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ORDER BY titulo<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LIMIT 10;<br>This query take more than 10 secods, and i think this is too much for such an easy query.<br>Every night, i execute a VACUUM ANALYZE over my data base.<br><br>The query plan of this query, is the next one:
<br>QUERY PLAN<br>Limit&nbsp; (cost=4725.18..4725.20 rows=10 width=56) (actual time=17060.826..17061.078 rows=10 loops=1)<br>&nbsp; -&gt;&nbsp; Sort&nbsp; (cost=4725.18..4728.23 rows=1223 width=56) (actual time=17060.806..17060.874 rows=10 loops=1)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: titulo<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Heap Scan on libros&nbsp; (cost=45.28..4662.46 rows=1223 width=56) (actual time=10831.530..16957.667 rows=2542 loops=1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: (idxts2_titsub @@ &#39;&#39;&#39;sevilla&#39;&#39;&#39;::tsquery)
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Index Scan on idxts2_titsub_idx&nbsp; (cost=0.00..45.28 rows=1223 width=0) (actual time=10830.051..10830.051 rows=2586 loops=1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (idxts2_titsub @@ &#39;&#39;&#39;sevilla&#39;&#39;&#39;::tsquery)
<br>Total runtime: 17062.665 ms<br><br>I have no idea what is happening. Why the Bitmap Index Scan and the Bitmap Heap Scan cost so much time?<br><br>I have a&nbsp; 2GB RAM memory Server. <br><br>Thanks every body for your healp and sorry for my English
<br><br>

------=_Part_46790_6655762.1171968114849--