Skip site navigation (1) Skip section navigation (2)

GIN index not used

From: Mark <Marek(dot)Balgar(at)seznam(dot)cz>
To: pgsql-general(at)postgresql(dot)org
Subject: GIN index not used
Date: 2011-04-27 20:55:51
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
I have problem with GIN index. Queries over it takes a lot of time. Some
I've got a table with tsvector- textvector:
CREATE TABLE mediawiki.pagecontent
  old_id integer NOT NULL DEFAULT
  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 @@
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

View this message in context:
Sent from the PostgreSQL - general mailing list archive at


pgsql-general by date

Next:From: MarkDate: 2011-04-27 20:57:58
Subject: GIN index not used
Previous:From: Doug GorleyDate: 2011-04-27 20:35:27
Subject: Re: Different sort order btwn Linux and Windows

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group