Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 052E6B5DBD8 for ; Wed, 15 Jun 2011 14:28:49 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 87119-02 for ; Wed, 15 Jun 2011 17:28:42 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from omr1.networksolutionsemail.com (omr1.networksolutionsemail.com [205.178.146.51]) by mail.postgresql.org (Postfix) with ESMTP id 54D6BB5D801 for ; Wed, 15 Jun 2011 14:28:42 -0300 (ADT) Received: from cm-omr12 (mail.networksolutionsemail.com [205.178.146.50]) by omr1.networksolutionsemail.com (8.13.6/8.13.6) with ESMTP id p5FHSfbt017746 for ; Wed, 15 Jun 2011 13:28:41 -0400 X-Authenticated-IP: 205.178.146.50 Received: from [205.178.146.50] ([205.178.146.50:37844] helo=webmail12) by cm-omr12 (envelope-from ) (ecelerity 2.2.2.41 r(31179/31189)) with ESMTP id F7/B7-29882-9CBE8FD4; Wed, 15 Jun 2011 13:28:41 -0400 Received: from 65.222.202.202 (mark@remingtondatabasesolutions.com [65.222.202.202]) by webmail12 (Netsol 11.2.30) with WEBMAIL id 1308; Wed, 15 Jun 2011 17:28:41 +0000 From: "Mark Johnson" To: "Kevin Grittner" , "Tim" , pgsql-admin@postgresql.org, "Greg Williamson" Importance: Normal Sensitivity: Normal Message-ID: X-Mailer: Network Solutions Webmail, Build 11.2.30 X-Originating-IP: [65.222.202.202] X-Forwarded-For: [(null)] Date: Wed, 15 Jun 2011 17:28:41 +0000 Subject: Re: tsvector limitations MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="--=_vm_0011_W5651513608_1308_1308158921" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.735 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_NUMERIC_HELO=1.164 X-Spam-Level: X-Archive-Number: 201106/82 X-Sequence-Number: 37708 ----=_vm_0011_W5651513608_1308_1308158921 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable When this discussion first started, I immediately thought about people wh= o full text index their server's log files. As a test I copied /var/log/m= essages to $PGDATA and then used the same pg_read_file() function you men= tioned 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_c= olumn_size reported 201557. So in this test a 4.2 MB text file produced a= tsvector of size 200 KB. If this scales linearly, then the max size of a= n input document would be 21.8 MB before you hit the tsvector limit of 1 = MB. If you run a "find" command on your server for files larger than 20 M= B, the percent is quite small maybe 1% of files. In the specific case of = indexing postgresql's log files, you could use parameter log_rotation_siz= e to ensure all files are smaller than N and avoid the tsvector limits. -Mark -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: Wednesday, June 15, 2011 12:39 PM To: 'Tim', pgsql-admin@postgresql.org, 'Greg Williamson' Subject: Re: [ADMIN] tsvector limitations Greg Williamson wrote: > Try trolling texts at the Internet Archive (arch= ive.org) -- lots > of stuff that has been rendered into ASCII ... Governm= ent > documents and the like from all periods; novels and the like that >= are no longer under copyright, so lots of long classics. > > > > for exa= mple ... 765K Thanks. OK, for perspactive, A Tale of Two Cities has a tsv= ector size of 121KB. -Kevin -- Sent via pgsql-admin mailing list (pgsql-a= dmin@postgresql.org) To make changes to your subscription: http://www.pos= tgresql.org/mailpref/pgsql-admin ----=_vm_0011_W5651513608_1308_1308158921 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
 

When this discussion first started, I im= mediately thought about people who full text index their server's log fil= es.  As a test I copied /= var/log/messages to $PGDATA and then used the same pg_read_file() functio= n 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  the= n added a column named tsv of type tsvector, and populated it using to_ts= vector().  The function pg_column_size reported 201557.  So in this test a 4.2 MB text file produced a tsvector of size 200 KB.&n= bsp; If this scales linearly, then the max size of an input document woul= d be 21.8 MB before you hit the tsvector limit of 1 MB.  If you run = a "find" command on your server for files larger than 20 MB, the percent = is quite small maybe 1% of files.  In the spec= ific case of indexing postgresql's log files, you could use parameter log= _rotation_size to ensure all files are smaller than N and avoid the tsvec= tor limits. 
-Mark

-----Original Message-----
= From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent:= Wednesday, June 15, 2011 12:39 PM
To: 'Tim', pgsql-admin@p= ostgresql.org, 'Greg Williamson'
Subject: Re: [ADMIN] tsvector = limitations

Greg Williamson wrote:= > Try trolling texts at the Internet Archive (archive.org) -- lots &g= t; of stuff that has been rendered into ASCII ... Government > documen= ts and the like from all periods; novels and the like that > are no lo= nger under copyright, so lots of long classics. > > > > f= or example ... 765K Thanks. OK, for perspactive, A Tale of Two Cities has= a tsvector size of 121KB. -Kevin -- Sent via pgsql-admin mailing list (p= gsql-admin@postgresql.org) To make changes to your subscription: http://w= ww.postgresql.org/mailpref/pgsql-admin
----=_vm_0011_W5651513608_1308_1308158921--