pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tim <elatllat@gmail.com>
To: Craig James <craig_james@emolecules.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: tsvector limitations
Date: Tue, 14 Jun 2011 18:33:41 -0400
Message-ID: <BANLkTi=Rz9imi7eKF7DmQigjut5pgQJYSg@mail.gmail.com> (raw)
In-Reply-To: <4DF7CEA5.4090400@emolecules.com>
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>
	<4DF7CEA5.4090400@emolecules.com>

Hi Craig,

Thanks for writing.

If one were to try to increase the limitation of tsvectors (I'm not sure I
need to yet; this thread is mainly to determine that.)
Instead of using a solution involving a "vocabulary" file,
one would probably be better off discarding tsvectors making a vocabulary
table then linking it to documents with a (dict_id, hit_count, word_id)
table
It would be faster, smaller, and more accurate because it would not contain
the now useless position information, while it would contain the otherwise
lost word count information.

I wonder if anyone has any incite on the inner workings of ORACLE/MSSQL/etc
FTS.
Maybe there is a common design pattern I/we can use to handle text files of
non trivial vocabulary that preserves position information.
I'm not sure a (dict_id, position, word_id) table would be queryable in a
useful way
because as far as I can think at the moment there is no good way to compare
the position in different rows with SQL.

I will collect a few more sample files to see how much I relay need this
.... multi-language files are probably the worst offenders.



On Tue, Jun 14, 2011 at 5:12 PM, Craig James <craig_james@emolecules.com>wrote:

>  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
>
>

view thread (21+ messages)  latest in thread

Message-ID: <BANLkTi=Rz9imi7eKF7DmQigjut5pgQJYSg@mail.gmail.com>
Permalink:  ../BANLkTi=Rz9imi7eKF7DmQigjut5pgQJYSg@mail.gmail.com/
Also on:    postgresql.org/message-id/BANLkTi=Rz9imi7eKF7DmQigjut5pgQJYSg@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: elatllat@gmail.com, craig_james@emolecules.com
  Subject: Re: tsvector limitations
  In-Reply-To: <BANLkTi=Rz9imi7eKF7DmQigjut5pgQJYSg@mail.gmail.com>
  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> <4DF7CEA5.4090400@emolecules.com> <BANLkTi=Rz9imi7eKF7DmQigjut5pgQJYSg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox