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

Re: LIKE search and performance

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Craig James <craig_james(at)emolecules(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: LIKE search and performance
Date: 2007-05-24 21:46:54
Message-ID: 20070524214654.GR4320@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-performance
Craig James wrote:
> Mark Lewis wrote:
> 
> >PG could scan the index looking for matches first and only load the
> >actual rows if it found a match, but that could only be a possible win
> >if there were very few matches, because the difference in cost between a
> >full index scan and a sequential scan would need to be greater than the
> >cost of randomly fetching all of the matching data rows from the table
> >to look up the visibility information.  
> 
> 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.

-- 
Alvaro Herrera                         http://www.flickr.com/photos/alvherre/
"Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente"

In response to

Responses

pgsql-performance by date

Next:From: markDate: 2007-05-24 21:54:51
Subject: Re: LIKE search and performance
Previous:From: Craig JamesDate: 2007-05-24 21:23:48
Subject: Re: LIKE search and performance

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