Re: GiST or GIN, I feel like I am doing something wrong

Aaron,

did you actually check performance of search in both cases ?
GiST index can be small but very inefficient, since top-level
signatures can be degenerated, so we just remove them. It's easy to
see that looking in explain analyze - see difference between number of
rows found by index and actual number after recheck. And remember, recheck
needs access to the heap, which is slow and can kill performance.
GIN is big, but it should be fine for your setup. Also, see explain analyze.

Oleg
On Wed, 17 Jun 2009, Aaron wrote:

> We are testing full text searching on a small chunk of our data. We
> have created an INDEX to make searching faster. From the PostgreSQL
> 8.3 docs, we are running 8.3.7, it seems we should be running GIN
> indexes. The reason GIN on paper seems like the right INDEX:
> * we have static data
> * we have over 241071 unique words (lexemes)
> * GIN index lookups are about three times faster and we are 99.9% searching
>
> The problem is that we have been testing with both INDEX types and
> GiST is killing GIN. I believe it has to do with the size of our GiST
> index.
> SELECT * from relation_size where relation like '%full%';
> relation | size
> --------------------------------------------+--------
> public.profile_images_fulltext_gin | 437 MB
> public.profile_images_fulltext | 161 MB
> public.profile_images_fulltext_gist | 66 MB
> public.profile_images_fulltext_pif_key_key | 18 MB
> (4 rows)
>
> So my questions...
> Why is the GiST index so large?
> Would the large size likely effect performance?
> Am I doing something fundamentally wrong?
> Yes I was sure to ANALYZE public.profile_images_fulltext between all
> my INDEX DROP and CREATE
>
> More details:
> owl=# \d profile_images_fulltext
> Table "public.profile_images_fulltext"
> Column | Type | Modifiers
> ------------------+-----------------------------+---------------
> pif_key | bigint | not null
> content | tsvector |
> datetime_created | timestamp without time zone | default now()
> raw | text |
>
> owl=# SELECT count (pif_key) from public.profile_images_fulltext;
> count
> --------
> 630699
> (1 row)
>
> owl=# SELECT count(word) FROM ts_stat('SELECT content FROM
> profile_images_fulltext');
> count
> --------
> 241071
> (1 row)
>
> CREATE INDEX profile_images_fulltext_gin ON profile_images_fulltext
> USING gin(content);
> CREATE INDEX profile_images_fulltext_gist ON profile_images_fulltext
> USING gist(content);
>
>
>
> Any and all thoughts would be greatly appreciated,
> Aaron Thul
> http://www.chasingnuts.com
> Life is complex: it has real and imaginary components.
>
>

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