Search Postgresql Archives

Having performance problems with TSearch2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.28 rows=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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux