GIN index not used

I have problem with GIN index. Queries over it takes a lot of time. Some
informations:

I've got a table with tsvector- textvector:
CREATE TABLE mediawiki.pagecontent
(
old_id integer NOT NULL DEFAULT
nextval('mediawiki.text_old_id_seq'::regclass),
old_text text,
old_flags text,
textvector tsvector,
CONSTRAINT pagecontent_pkey PRIMARY KEY (old_id)
)
The table has about 311 000 rows.
I've created GIN index over textvector:
CREATE INDEX gin_index2 ON mediawiki.pagecontent USING gin (textvector);

At first all was ok, but last days I find out that the queries take a lot of
time (10sec and more). When I start EXPLAIN ANALIZE of the query
SELECT old_id FROM mediawiki.pagecontent WHERE (textvector @@
(to_tsquery('den')))
the result is this:
"Bitmap Heap Scan on pagecontent (cost=8677.26..26663.01 rows=9037 width=4)
(actual time=8.258..8131.677 rows=9093 loops=1)"
" Recheck Cond: (textvector @@ to_tsquery('den'::text))"
" -> Bitmap Index Scan on gin_index2 (cost=0.00..8675.00 rows=9037
width=0) (actual time=6.002..6.002 rows=9093 loops=1)"
" Index Cond: (textvector @@ to_tsquery('den'::text))"
"Total runtime: 8150.949 ms"

It seems that the GIN index was not used. At first I though, that there is
some mess in the db, cause I provided some experiments(delete and import
data and GIN index), so I used vacuum, afterwards I tried pg_dump and
pg_restore, but it did not help.
Could you please point me in the right direction, where could be the
problem?
Thanks a lot
Mark

--
View this message in context: http://postgresql.1045698.n5.nabble.com/GIN-index-not-used-tp4344826p4344826.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

It should be better in 9.1
http://archives.postgresql.org/message-id/4C2DDC9B.1060908@sigaev.ru

Oleg
On Wed, 27 Apr 2011, Mark wrote:

> I have problem with GIN index. Queries over it takes a lot of time. Some
> informations:
>
> I've got a table with tsvector- textvector:
> CREATE TABLE mediawiki.pagecontent
> (
> old_id integer NOT NULL DEFAULT
> nextval('mediawiki.text_old_id_seq'::regclass),
> old_text text,
> old_flags text,
> textvector tsvector,
> CONSTRAINT pagecontent_pkey PRIMARY KEY (old_id)
> )
> The table has about 311 000 rows.
> I've created GIN index over textvector:
> CREATE INDEX gin_index2 ON mediawiki.pagecontent USING gin (textvector);
>
> At first all was ok, but last days I find out that the queries take a lot of
> time (10sec and more). When I start EXPLAIN ANALIZE of the query
> SELECT old_id FROM mediawiki.pagecontent WHERE (textvector @@
> (to_tsquery('den')))
> the result is this:
> "Bitmap Heap Scan on pagecontent (cost=8677.26..26663.01 rows=9037 width=4)
> (actual time=8.258..8131.677 rows=9093 loops=1)"
> " Recheck Cond: (textvector @@ to_tsquery('den'::text))"
> " -> Bitmap Index Scan on gin_index2 (cost=0.00..8675.00 rows=9037
> width=0) (actual time=6.002..6.002 rows=9093 loops=1)"
> " Index Cond: (textvector @@ to_tsquery('den'::text))"
> "Total runtime: 8150.949 ms"
>
> It seems that the GIN index was not used. At first I though, that there is
> some mess in the db, cause I provided some experiments(delete and import
> data and GIN index), so I used vacuum, afterwards I tried pg_dump and
> pg_restore, but it did not help.
> Could you please point me in the right direction, where could be the
> problem?
> Thanks a lot
> Mark
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/GIN-index-not-used-tp4344826p4344826.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83