X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 208919FA435
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 12 Sep 2006 09:47:18 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 39925-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 12 Sep 2006 09:47:10 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.mnc.ch (62-2-77-205.static.cablecom.ch [62.2.77.205])
	by postgresql.org (Postfix) with ESMTP id 5C38D9FA1E2
	for <pgsql-performance@postgresql.org>;
	Tue, 12 Sep 2006 09:47:10 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by mail.mnc.ch (Postfix) with ESMTP id 1E167A82CB;
	Tue, 12 Sep 2006 14:52:19 +0200 (CEST)
Received: from mail.mnc.ch ([127.0.0.1])
	by localhost (mail.test.lan [127.0.0.1]) (amavisd-new,
	port 10025) with ESMTP
	id 17038-04; Tue, 12 Sep 2006 14:52:17 +0200 (CEST)
Received: from meuh.mnc.lan (62-2-77-204.static.cablecom.ch [62.2.77.204])
	by mail.mnc.ch (Postfix) with ESMTP id B46AAA82B7;
	Tue, 12 Sep 2006 14:52:17 +0200 (CEST)
Received: by meuh.mnc.lan (Postfix, from userid 1000)
	id 3DFEC183650; Tue, 12 Sep 2006 14:46:18 +0200 (CEST)
To: Laszlo Nagy <gandalf@designaproduct.biz>
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>
X-Hashcash: 
 1:20:060912:gandalf@designaproduct.biz::Fpru2KS9fI6WNOAq:000000000000000000000000000000000001fAy
X-Hashcash: 
 1:20:060912:pgsql-performance@postgresql.org::FJAtt/b14zbjL7UI:000000000000000000000000000006uwT
From: Guillaume Cottenceau <gc@mnc.ch>
Date: 12 Sep 2006 14:46:18 +0200
In-Reply-To: <4506A9E7.2060303@designaproduct.biz>
Message-ID: <87y7spw8v9.fsf@meuh.mnc.lan>
Lines: 18
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: amavisd-new at mnc.ch
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.178 tagged_above=0 required=5 tests=AWL, BIZ_TLD,
	FORGED_RCVD_HELO
X-Spam-Level: *
X-Archive-Number: 200609/79
X-Sequence-Number: 20765

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:

http://www.postgresql.org/docs/7.4/interactive/runtime-config.html#RUNTIME-CONFIG-QUERY

-- 
Guillaume Cottenceau
Create your personal SMS or WAP Service - visit http://mobilefriends.ch/