Skip site navigation (1) Skip section navigation (2)

Re: Full Text Indexing

From: "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au>
To: "Hackers" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Full Text Indexing
Date: 2001-08-31 01:43:03
Message-ID: ECEHIKNFIMMECLEBJFIGAELICBAA.chriskl@familyhealth.com.au (view raw or flat)
Thread:
Lists: pgsql-hackers
Doh - sorry about these hideously late posts.  I think my mail queue has
been clogged up for a while!

Chris

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Christopher
> Kings-Lynne
> Sent: Monday, 23 July 2001 10:34 AM
> To: Tom Lane
> Cc: Hackers
> Subject: Re: [HACKERS] Full Text Indexing
>
>
> > "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> writes:
> > > I'm playing around with the Full Text Indexing module, and I
> notice that
> > > it's case-sensitive.  This seems to be pretty useless to me -
> > especially for
> > > my application.  I wonder if there'd be any objections to me
> > modifying it to
> > > be case-insensitive.  Or at least be configurable either way...
> >
> > Seems like a good idea, but make it configurable.
>
> I actually came up with another way of solving the problem.
>
> The FTI table has two columns: (string, id).  The code needs to do two
> things; delete all strings for an id, and join to the main table based on
> the id.
>
> The docs for FTI recommend indexing (string, id).  This is poor as the
> delete based on id does a sequential scan, although the join seems to be
> able to use the index (as long was you have a where string ~
> '^something').
>
> I indexed as follows:
>
> -- Functional index that lets us do case-insensitivity without hacking
> fti.so
> CREATE INDEX fti_string_idx ON fti_table(lower(string));
>
> -- Index on id to allow fast deletes
> CREATE INDEX fti_id_idx ON fti_table(id);
>
> That seems to be a good solution to me - it allows
> case-insensitivity, fast
> deletion and fast joining.
>
> > > Also, the fti.pl that comes with the contrib seems to be using
> > an outdated
> > > version of CPAN's Pg.pm.
> >
> > It hasn't been touched in awhile, so feel free to update it.  BTW,
> > someone ought to look at bringing src/interfaces/perl5 into sync with
> > the CPAN version, too.  Or possibly we should stop distributing that
> > altogether, if the CPAN copy is being maintained?
>
> I'll have a look someday maybe, but I'll try to get this
> harder-than-expected ADD CONSTRAINT UNIQUE/PRIMARY patch off my
> hands first.
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>


In response to

pgsql-hackers by date

Next:From: Tatsuo IshiiDate: 2001-08-31 02:06:00
Subject: Re: Majordomo being upgraded ...
Previous:From: Doug McNaughtDate: 2001-08-31 01:35:42
Subject: Re: Multiple semicolon separated statements and autocommit

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group