Having performance problems with TSearch2

From: "Rafa Comino" <rafacomino(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: Having performance problems with TSearch2
Date: 2007-02-20 10:41:54
Message-ID: bd8b58a40702200241y7ad2ae60q4e39bbfcfe1eed2f@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rafa Comino 2007-02-20 11:10:39 Having performance problems with TSearch2
Previous Message Csaba Nagy 2007-02-20 08:54:19 Re: How to force disconnections from a Perl script?

Browse pgsql-performance by date

  From Date Subject
Next Message Teodor Sigaev 2007-02-20 11:15:27 Re: Having performance problems with TSearch2
Previous Message Tom Lane 2007-02-20 07:43:06 Re: slow subselects