Re: LIKE search and performance

Alvaro Herrera wrote:
 >> Just out of curiosity: Does Postgress store a duplicate of the data in the
>> index, even for long strings?  I thought indexes only had to store the
>> string up to the point where there was no ambiguity, for example, if I have
>> "missing", "mississippi" and "misty", the index only needs "missin",
>> "missis" and "mist" in the actual index.
>
> What would happen when you inserted a new tuple with just "miss"?  You
> would need to expand all the other tuples in the index.

That's right.  This technique used by some index implementations is a tradeoff between size and update speed.  Most
wordsin most natural languages can be distinguished by the first few characters.  The chances of having to modify more
thana few surrounding nodes when you insert "miss" is small, so some implementations choose this method.  Other
implementationschoose to store the full string.  I was just curious which method Postgres uses. 

Craig