Обсуждение: Full-text Indexing and Primary Keys

Folks,

1. Can anyone explain to me what "full-text indexing" is, and why we do
or don't need it for Postgres?  The marketing types keep asking me about
it ("buzzword o' the day") and I don't have an answer for them.

2. I propose that future versions of PostgreSQL require a primary key at
table creation.  Frankly, I'm a little mystified as to why this was not
done already, but it's not too late to correct ...

-Josh



______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Josh Berkus wrote:

> 1. Can anyone explain to me what "full-text indexing" is, and why we do
> or don't need it for Postgres?  The marketing types keep asking me about
> it ("buzzword o' the day") and I don't have an answer for them.

Full text indexing: something like google does for HTML pages, or
fts.postgresql.org does for the PostgresSQL mailing list archives.

Good full text indexing programs understand something about the
language they're indexing and automatically deal with plurals,
suffixes, etc.

Full text indexing is useful for many applications that include text
data along with other more structured information. It is painful to
have to use SQL plus some other facility for searching; just for
starters it usually means keeping a second copy of the data.

I suspect the push for full text indexing in PostgreSQL is stronger
now that the 8KB row size limit that "ancient" releases had is gone.

> 2. I propose that future versions of PostgreSQL require a primary key at
> table creation.  Frankly, I'm a little mystified as to why this was not
> done already, but it's not too late to correct ...

SQL uses a "bag" model, not a "set" model.  Duplicates are allowed.  I
doubt that PostgreSQL wants to step away from both the standards and
current practice in this area.

Regards,

Giles

Josh,

Full Text Indexing

It will allow you to store text into a database and find portions of this
text again, based on a few words of the text in the record.
Say, for example, you want to store the bible in a PostGres database. You
will store these fields:
1. Book
2. Chapter
3. Verse
4. Verse text

If you know your bible, you probably can find any passage by just going to
book/chapter/verse directly. But if you're not, and you just want to find
all verses, where "Nathan", "Solomon" and "mother" is mentioned, you need
something which lets you do that - and fast.

What you want is something which does:
select verse_text from bible where verse_text contains 'Nathan & Solomon &
mother';

or even:
select verse_text from bible where verse_text contains 'Nath* & Solo* &
moth*';

This would be similar to "find file on hard drive by content" - which, if
not indexed, takes forever.

Hope this makes the issue a little more clear.

Best regards,
Chris



----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: <pgsql-sql@postgresql.org>
Sent: Monday, July 23, 2001 10:32 AM
Subject: [SQL] Full-text Indexing and Primary Keys


> Folks,
>
> 1. Can anyone explain to me what "full-text indexing" is, and why we do
> or don't need it for Postgres?  The marketing types keep asking me about
> it ("buzzword o' the day") and I don't have an answer for them.
>
> 2. I propose that future versions of PostgreSQL require a primary key at
> table creation.  Frankly, I'm a little mystified as to why this was not
> done already, but it's not too late to correct ...
>
> -Josh
>
>
>
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                        Josh Berkus
>   Complete information technology      josh@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco
>


----------------------------------------------------------------------------
----


>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com