Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 270269FC028
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 24 May 2007 17:51:14 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 69354-04 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 24 May 2007 17:51:06 -0300 (ADT)
X-Greylist: delayed 02:04:07.771486 by SQLgrey-1.7.5
Received: from pih-relay05.plus.net (pih-relay05.plus.net [212.159.14.132])
	by postgresql.org (Postfix) with ESMTP id 4FA169FC021
	for <pgsql-performance@postgresql.org>;
	Thu, 24 May 2007 17:51:09 -0300 (ADT)
Received: from [80.229.150.39] (helo=mansionfamily.plus.com)
	by pih-relay05.plus.net with esmtp (Exim) id 1HrKH2-0001na-V4
	for pgsql-performance@postgresql.org; Thu, 24 May 2007 21:51:05 +0100
Received: from [192.168.0.87] ([192.168.0.87]:3720)
	by mansionfamily.plus.com with [XMail 1.22 ESMTP Server]
	id <S1BB6> for <pgsql-performance@postgresql.org> from
	<james@mansionfamily.plus.com>; Thu, 24 May 2007 21:52:29 +0100
Message-ID: <4655FB8A.1040707@mansionfamily.plus.com>
Date: Thu, 24 May 2007 21:54:34 +0100
From: James Mansion <james@mansionfamily.plus.com>
User-Agent: Thunderbird 2.0.0.0 (Windows/20070326)
MIME-Version: 1.0
To: Magnus Hagander <magnus@hagander.net>
CC: Alexander Staubo <alex@purefiction.net>, Andy <frum@ar-sd.net>,
	pgsql-performance@postgresql.org
Subject: Re: LIKE search and performance
References: <009501c79d4c$445428f0$0b00a8c0@mpsro.dom>
	<88daf38c0705230905u1a981102k691b47bd36011a80@mail.gmail.com>
	<4655DE75.4080506@mansionfamily.plus.com>
	<4655E64B.5050503@hagander.net>
In-Reply-To: <4655E64B.5050503@hagander.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200705/478
X-Sequence-Number: 24868


> 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.