Re: Performance for seq. scans

On Wed, Jul 26, 2000 at 10:34:39AM -0400, Mitch Vincent wrote:
> The FTI trigger code that's distributed with PostgreSQL now actually breaks
> the words up into two character substrings.
>
> I re-wrote it to eliminate duplicates and only split up the words based on
> whitespace and delimiters -- if you did this you could still use LIKE to
> match based on substrings, then you would have the added speed of an index
> scan..
>
> Jules:
>
> select * from table_a where foo like '%bar%'
>
> Depending on the table type of foo, that doesn't have to do a seq scan... If
> it's anything but text, you can create an index on it -- LIKE can use
> indexes. If it is type text then I would look into using the FTI stuff in
> contrib. If you want mine, let me know however it sounds like the
> distributed version would be more suited to what you'd like to do.

Hmm. Colour me confused.

You're suggesting I can use a conventional index on 'foo'? Well,
certainly, there is such an index on the table anyhow (since some
queries do use it) but I don't understand. Surely LIKE only uses
indexes when the string match is anchored on the left? Certainly
that's my understanding of indexes, and it's what the docs say...

OTOH, are you saying that the FTI code in 7.0.2 contrib does in fact
do the 'index all occuring 2-char substring' trick? I must go and
investigate that straight away. Many thanks.

Jules