Tsearch Index Size and GiST vs. GIN

On Mon, 2006-11-06 at 13:14 -0600, Richard Whidden wrote:

Due to popular demand (1 person), I've compared sizes with 90 and 100
fillfactors, along with using the new GIN index.

Findings were not surprising, except for the GIN indexes, which doubled in
size.

This is how I understand it after reading up on the subject:

GIN inserts many index entries for every record inserted in the table,
and each index entry points to many tuples.

GiST is a one-to-one relationship, one insert causes one index entry to
be inserted and that points to one record.

After several ALTER/RE INDEXes, here they are:

GiST
----
8.1 = 94990
8.2 FF90 = 106244 relpages (8k)
8.2 FF100 = 95049

GIN
---
FF100 = 197702

Regards,
Jeff Davis

Richard Whidden wrote:

Due to popular demand (1 person), I've compared sizes with 90 and 100
fillfactors, along with using the new GIN index.

First, GIN doesn't utilize fillfactor option yet.

Findings were not surprising, except for the GIN indexes, which doubled
in size.

Second, In opposite to GiST, size of GIN index strongly depends on document
collection. Each unique word in collection has list of pointer to document in
which it occur. Size of pointer is a 6 byte. So word with 4 bytes length will
occupy at least 4 + N_docs_with_word * 6.

Fortunately, for searching it's needed to read very small part of index, so GIN
will be faster.

After several ALTER/RE INDEXes, here they are:

GiST
----
8.1 = 94990
8.2 FF90 = 106244 relpages (8k)
8.2 FF100 = 95049

GIN
---
FF100 = 197702

Richard Whidden

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/