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

Re: LIKE search and performance

From: Craig James <craig_james(at)emolecules(dot)com>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: LIKE search and performance
Date: 2007-05-24 22:08:16
Message-ID: 46560CD0.404@emolecules.com (view raw or flat)
Thread:
Lists: pgsql-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 words in most natural languages can be distinguished by the first few characters.  The chances of having to modify more than a few surrounding nodes when you insert "miss" is small, so some implementations choose this method.  Other implementations choose to store the full string.  I was just curious which method Postgres uses.

Craig


In response to

pgsql-performance by date

Next:From: PFCDate: 2007-05-24 22:09:15
Subject: Re: LIKE search and performance
Previous:From: markDate: 2007-05-24 21:54:51
Subject: Re: LIKE search and performance

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