To: "Mark Johnson" <mark@remingtondatabasesolutions.com>
cc: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>,
 "Tim" <elatllat@gmail.com>,
	pgsql-admin@postgresql.org, "Greg Williamson" <gwilliamson39@yahoo.com>
Subject: Re: tsvector limitations 
In-reply-to: <W565151360813081308158921@webmail12> 
References: <W565151360813081308158921@webmail12>
Comments: In-reply-to "Mark Johnson" <mark@remingtondatabasesolutions.com>
	message dated "Wed, 15 Jun 2011 17:28:41 -0000"
Date: Wed, 15 Jun 2011 14:31:28 -0400
Message-ID: <18955.1308162688@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Mark Johnson" <mark@remingtondatabasesolutions.com> writes:
> When this discussion first started, I immediately thought about people
> who full text index their server's log files. As a test I copied
> /var/log/messages to $PGDATA and then used the same pg_read_file()
> function you mentioned earlier to pull the data into a column of type
> text. The original file was 4.3 MB, and the db column had length
> 4334920 and the function pg_column_size reported a size of 1058747. I
> then added a column named tsv of type tsvector, and populated it using
> to_tsvector(). The function pg_column_size reported 201557. So in this
> test a 4.2 MB text file produced a tsvector of size 200 KB. If this
> scales linearly,

... which it won't.  There is no real-world full text indexing
application where there aren't many duplications of words.  (The OP
eventually admitted that his "test case" was a dictionary word list
and not an actual document.)  Any discussion of required tsvector
sizes that doesn't account for the actual, nonlinear scaling behavior
isn't worth the electrons it's printed on.

			regards, tom lane