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: (view raw or whole 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               
"Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente"

In response to


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-2015 The PostgreSQL Global Development Group