X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id C2A349FA435
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 12 Sep 2006 10:25:01 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 45408-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 12 Sep 2006 13:24:53 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from webmail.enterprisedb.com (edb04.managed.contegix.com
	[63.246.7.176]) by postgresql.org (Postfix) with ESMTP id 63D789FA1E2
	for <pgsql-performance@postgresql.org>;
	Tue, 12 Sep 2006 10:24:53 -0300 (ADT)
Received: from [192.168.0.20] (unknown [62.232.55.118])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(Client did not present a certificate)
	by webmail.enterprisedb.com (Postfix) with ESMTP id 3BA02A24A79;
	Tue, 12 Sep 2006 08:24:51 -0500 (CDT)
Message-ID: <4506B508.80806@enterprisedb.com>
Date: Tue, 12 Sep 2006 14:24:24 +0100
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Thunderbird 1.5.0.5 (X11/20060812)
MIME-Version: 1.0
To: Guillaume Cottenceau <gc@mnc.ch>
CC: pgsql-performance@postgresql.org
Subject: Re: Poor performance on seq scan
References: <450684EC.4090304@designaproduct.biz>
	<4506902C.7090407@enterprisedb.com>	<4506A9E7.2060303@designaproduct.biz>
	<87y7spw8v9.fsf@meuh.mnc.lan>
In-Reply-To: <87y7spw8v9.fsf@meuh.mnc.lan>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=2.148 tagged_above=0 required=5 tests=BIZ_TLD,
	FORGED_RCVD_HELO
X-Spam-Level: **
X-Archive-Number: 200609/80
X-Sequence-Number: 20766

Guillaume Cottenceau wrote:
> Laszlo Nagy <gandalf 'at' designaproduct.biz> writes:
>   
>>> Probably, but PostgreSQL doesn't know how to do that. Even if it
>>> did, it depends on how many matches there is. If you scan the index
>>> and then fetch the matching rows from the heap, you're doing random
>>> I/O to the heap. That becomes slower than scanning the heap
>>> sequentially if you're going to get more than a few hits.
>>>       
>> I have 700 000 rows in the table, and usually there are less than 500
>> hits. So probably using a "seq index scan" would be faster. :-) Now I
>>     
>
> You can confirm this idea by temporarily disabling sequential
> scans. Have a look at this chapter:
>   

I don't think it will anyway do a "seq index scan" as Laszlo envisions. 
PostgreSQL cannot do "fetch index tuple and apply %Mug% to it. If it 
matches, fetch heap tuple". Even if you disable sequential scans, it's 
still going to fetch every heap tuple to see if it matches "%Mug%". It's 
just going to do it in index order, which is slower than a seq scan.

BTW:  in addition to setting enable_seqscan=false, you probably have to 
add a dummy where-clause like "name > ''" to force the index scan.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com