Re: GIN index not used

From: Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>
To: Mark <Marek(dot)Balgar(at)seznam(dot)cz>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: GIN index not used
Date: 2011-04-28 05:33:36
Message-ID: DE13A728-BAB1-4347-89AF-55146F3F306D@solfertje.student.utwente.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 27 Apr 2011, at 22:55, Mark wrote:

> 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);

> 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.

But it _is_ being used. It takes almost 1ms per row it finds though, I wonder why that is. Maybe you're using slow hardware or you didn't provide PG with enough resources to work with efficiently?

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4db8fc3811671483513299!

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Golub 2011-04-28 05:37:10 Re: PostgreSQL Core Team
Previous Message pasman pasmański 2011-04-28 05:29:13 New feature: skip row locks when table is locked.