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

Re: LIKE search and performance

From: James Mansion <james(at)mansionfamily(dot)plus(dot)com>
To: Magnus Hagander <magnus(at)hagander(dot)net>
Cc: Alexander Staubo <alex(at)purefiction(dot)net>, Andy <frum(at)ar-sd(dot)net>, pgsql-performance(at)postgresql(dot)org
Subject: Re: LIKE search and performance
Date: 2007-05-24 20:54:34
Message-ID: 4655FB8A.1040707@mansionfamily.plus.com (view raw or flat)
Thread:
Lists: pgsql-performance
> If Sybase is still like SQL Server (or the other way around), it *may*
> end up scanning the index *IFF* the index is a clustered index. If it's
> a normal index, it will do a sequential scan on the table.
>
>   
Are you sure its not covered?  Have to check at work - but I'm off next 
week so it'll have to wait.

> It's not a win on PostgreSQL, because of our MVCC implementation. We
> need to scan *both* index *and* data pages if we go down that route, in
> which case it's a lot faster to just scan the data pages alone.
>
>   
Why do you need to go to all the data pages - doesn't the index 
structure contain all the keys so
you prefilter and then check to see if the *matched* items are still in 
view?  I'll be first to admit I
know zip about Postgres, but it seems odd - doesn't the index contain 
copies of the key values?.

I suspect that I mis-spoke with 'leaf'.  I really just mean 'all index 
pages with data', since the scan
does not even need to be in index order, just a good way to get at the 
data in a compact way.



In response to

Responses

pgsql-performance by date

Next:From: Mark LewisDate: 2007-05-24 21:02:40
Subject: Re: LIKE search and performance
Previous:From: Magnus HaganderDate: 2007-05-24 19:23:55
Subject: Re: LIKE search and performance

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