pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHaving performance problems with TSearch2
3+ messages / 3 participants
[nested] [flat]
* Having performance problems with TSearch2
@ 2007-02-20 10:41 Rafa Comino <rafacomino@gmail.com>
2007-02-20 11:15 ` Re: Having performance problems with TSearch2 Teodor Sigaev <teodor@sigaev.ru>
2007-03-05 12:59 ` Re: Having performance problems with TSearch2 Ares <abaidulin@rambler.ru>
0 siblings, 2 replies; 3+ messages in thread
From: Rafa Comino @ 2007-02-20 10:41 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql@postgresql.org; 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Having performance problems with TSearch2
2007-02-20 10:41 Having performance problems with TSearch2 Rafa Comino <rafacomino@gmail.com>
@ 2007-02-20 11:15 ` Teodor Sigaev <teodor@sigaev.ru>
1 sibling, 0 replies; 3+ messages in thread
From: Teodor Sigaev @ 2007-02-20 11:15 UTC (permalink / raw)
To: Rafa Comino <rafacomino@gmail.com>; +Cc: pgsql-general@postgresql.org
Use GIN index instead of GiST
> I have a table of books, with 1200000 registers. I have created an GIST
> index over the title and subtitle,
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Having performance problems with TSearch2
2007-02-20 10:41 Having performance problems with TSearch2 Rafa Comino <rafacomino@gmail.com>
@ 2007-03-05 12:59 ` Ares <abaidulin@rambler.ru>
1 sibling, 0 replies; 3+ messages in thread
From: Ares @ 2007-03-05 12:59 UTC (permalink / raw)
To: 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");
Your query didn't use index that you are created..
After CREATE INDEX you mast ran VACUUM (FULL recomended) and REINDEX
The query is: select userid,msg,idxfti from _my_msg0 where idxfti @@
to_tsquery('utf8_russian','хочу & трахаться');
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-03-05 12:59 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-20 10:41 Having performance problems with TSearch2 Rafa Comino <rafacomino@gmail.com>
2007-02-20 11:15 ` Teodor Sigaev <teodor@sigaev.ru>
2007-03-05 12:59 ` Ares <abaidulin@rambler.ru>
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