Re: GIN index not used

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!