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
hardwareor 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!