Message-ID: <4DF7CEA5.4090400@emolecules.com>
Date: Tue, 14 Jun 2011 14:12:05 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: tsvector limitations
References: <BANLkTi=hAv876hXEru6FO8PNGWFNYB21ZA@mail.gmail.com>
	<4DF7275C020000250003E59F@gw.wicourts.gov>
	<BANLkTikz+MY1DoO5UcxzJbG+VNgGKn0HFg@mail.gmail.com>
	<4DF7399B020000250003E5AB@gw.wicourts.gov>
	<BANLkTi=4EaMpXu7orXiAHOiA2AS0w9k_Yg@mail.gmail.com>
	<BANLkTiksUZuiKrtbsDfUkfYUU=zMEgK2cg@mail.gmail.com>
In-Reply-To: <BANLkTiksUZuiKrtbsDfUkfYUU=zMEgK2cg@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------080308070206020504030202"

This is a multi-part message in MIME format.
--------------080308070206020504030202
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 6/14/11 1:42 PM, Tim wrote:
> So I ran this test:
> unzip -p text.docx word/document.xml | perl -p -e 's/<.+?>/\n/g;s/[^a-z0-9\n]/\n/ig;'|grep ".." > text.txt
> ls -hal ./text.*
> #-rwxrwxrwx 1 postgres postgres 15M 2011-06-14 15:12 ./text.docx
> #-rwxrwxrwx 1 postgres postgres 29M 2011-06-14 15:17 ./text.txt
> mv /tmp/text.* /var/lib/postgresql/9.0/main/
> cd ~/;psql -d postgres
> #psql (9.0.4)
> CREATE DATABASE test;
> \q
> cd ~/;psql -d test
> CREATE TABLE test(title VARCHAR(256), data OID, words TSVECTOR);
> INSERT INTO test VALUES (  'text.docx',  LO_IMPORT('text.docx'),  TO_TSVECTOR(pg_read_file('text.txt' ,0, 100000000))  );
>
> and I got this:
> #ERROR:  string is too long for tsvector (30990860 bytes, max 1048575 bytes)
>
> doing the math
> echo "scale=3;29/(30990860/1048575)"|bc #==0.981
>
> Indicates a (worst case) limit of searching only the first 1MB of a text file before you need to start building your own search maybe on top of tsvector.
> The year is 2011 I don't think searching a 2MB text file is to much to expect.
The novel "Hawaii" at 960 pages is roughly 1MB.  tsvector was intended for documents (web pages, news articles, corporate memos, ...), not for books.  What you're asking for is interesting, but you can't complain that an open-source project that was designed for a different purpose doesn't meet your needs.
> So how am I to use the PGSQL FTS as a "full text search" when the above example can only handle a "small or partial text search"?
> If I'm not missing anything maybe the documentation should be adjusted accordingly.
Maybe a better question is, "So how am I to use PGSQL FTS as a "massively huge text search" when it was designed for nothing bigger than "huge text search"?
> Any thoughts or alternatives are most welcome.
I'm curious how tsvector could be useful on a 29 MB document.  That's roughly one whole encyclopedia set.  A document that size should have a huge vocabulary, and tsvector's index would be saturated.

However, if the vocabulary in this 29 MB document isn't that big, then you might consider creating a smaller "document."  You could write a Perl script that scans the document and creates a dictionary which it writes out as a secondary "vocabulary" file that's a list of the unique words in your document.  Create an auxillary column in your database to hold this vocabulary for each document, and use tsvector to index that.  The perl program would be trivial, and tsvector would be happy.

Craig


--------------080308070206020504030202
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
    <title></title>
  </head>
  <body bgcolor="#ffffff" text="#000000">
    On 6/14/11 1:42 PM, Tim wrote:
    <blockquote
      cite="mid:BANLkTiksUZuiKrtbsDfUkfYUU=zMEgK2cg@mail.gmail.com"
      type="cite">
      So I ran this test:<br>
      <div style="margin-left: 40px;">unzip -p text.docx
        word/document.xml | perl -p -e
        's/&lt;.+?&gt;/\n/g;s/[^a-z0-9\n]/\n/ig;'|grep ".." &gt;
        text.txt<br>
        ls -hal ./text.*<br>
        #-rwxrwxrwx 1 postgres postgres 15M 2011-06-14 15:12 ./text.docx<br>
        #-rwxrwxrwx 1 postgres postgres 29M 2011-06-14 15:17 ./text.txt<br>
        mv /tmp/text.* /var/lib/postgresql/9.0/main/<br>
        cd ~/;psql -d postgres<br>
        #psql (9.0.4)<br>
        CREATE DATABASE test;<br>
        \q<br>
        cd ~/;psql -d test<br>
        CREATE TABLE test(title VARCHAR(256), data OID, words TSVECTOR);<br>
        INSERT INTO test VALUES (&nbsp; 'text.docx',&nbsp;
        LO_IMPORT('text.docx'),&nbsp; TO_TSVECTOR(pg_read_file('text.txt' ,0,
        100000000))&nbsp; );<br>
        <br>
      </div>
      and I got this:<br>
      <div style="margin-left: 40px;">
        #ERROR:&nbsp; string is too long for tsvector (30990860 bytes, max
        1048575 bytes)<br>
        <br>
      </div>
      doing the math <br>
      <div style="margin-left: 40px;">echo
        "scale=3;29/(30990860/1048575)"|bc #==0.981<br>
      </div>
      <br>
      Indicates a (worst case) limit of searching only the first 1MB of
      a text file before you need to start building your own search
      maybe on top of tsvector.<br>
      The year is 2011 I don't think searching a 2MB text file is to
      much to expect.<br>
    </blockquote>
    The novel "Hawaii" at 960 pages is roughly 1MB.&nbsp; tsvector was
    intended for documents (web pages, news articles, corporate memos,
    ...), not for books.&nbsp; What you're asking for is interesting, but you
    can't complain that an open-source project that was designed for a
    different purpose doesn't meet your needs.<br>
    <blockquote
      cite="mid:BANLkTiksUZuiKrtbsDfUkfYUU=zMEgK2cg@mail.gmail.com"
      type="cite">So how am I to use the PGSQL FTS as a "full text
      search" when the above example can only handle a "small or partial
      text search"?<br>
      If I'm not missing anything maybe the documentation should be
      adjusted accordingly.<br>
    </blockquote>
    Maybe a better question is, "So how am I to use PGSQL FTS as a
    "massively huge text search" when it was designed for nothing bigger
    than "huge text search"?<br>
    <blockquote
      cite="mid:BANLkTiksUZuiKrtbsDfUkfYUU=zMEgK2cg@mail.gmail.com"
      type="cite">Any thoughts or alternatives are most welcome.<br>
    </blockquote>
    I'm curious how tsvector could be useful on a 29 MB document.&nbsp;
    That's roughly one whole encyclopedia set.&nbsp; A document that size
    should have a huge vocabulary, and tsvector's index would be
    saturated.<br>
    <br>
    However, if the vocabulary in this 29 MB document isn't that big,
    then you might consider creating a smaller "document."&nbsp; You could
    write a Perl script that scans the document and creates a dictionary
    which it writes out as a secondary "vocabulary" file that's a list
    of the unique words in your document.&nbsp; Create an auxillary column in
    your database to hold this vocabulary for each document, and use
    tsvector to index that.&nbsp; The perl program would be trivial, and
    tsvector would be happy.<br>
    <br>
    Craig<br>
    <br>
  </body>
</html>

--------------080308070206020504030202--