Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id C2EFC4763E7
	for <pgsql-sql@postgresql.org>; Mon, 20 Jan 2003 12:10:36 -0500 (EST)
Received: from resu1.ulb.ac.be (resu1.ulb.ac.be [164.15.59.200])
	by postgresql.org (Postfix) with ESMTP id 85B6C4763CE
	for <pgsql-sql@postgresql.org>; Mon, 20 Jan 2003 12:10:34 -0500 (EST)
Received: from mach.vub.ac.be (mach.ulb.ac.be [164.15.128.3]) by
	resu1.ulb.ac.be (8.8.8/3.17.1.ap (resu))
	id SAA29930; Mon, 20 Jan 2003 18:08:51 +0100 (MET) for
Received: from ulb.ac.be (geog-pc35.ulb.ac.be [164.15.134.155]) by
	mach.vub.ac.be (8.9.3/3.13.3.ap (mach))
	id SAA00738; Mon, 20 Jan 2003 18:10:39 +0100 (MET) for
Received: from 164.15.128.4 (SquirrelMail authenticated user mlennert)
	by http://moritz.homelinux.org with HTTP;
	Mon, 20 Jan 2003 18:10:24 +0100 (CET)
Message-ID: 
 <44591.164.15.128.4.1043082624.squirrel@http://moritz.homelinux.org>
In-Reply-To: <j19o2vo4hn15ghmmjfc40d2116ej4eg46u@4ax.com>
References: 
 <59665.164.15.128.4.1043062834.squirrel@http://moritz.homelinux.org>
	<1k3o2v01b5ceh937kibrojn2fjks3fc93t@4ax.com>  
	<10031.164.15.128.4.1043077332.squirrel@http://moritz.homelinux.org>
	<j19o2vo4hn15ghmmjfc40d2116ej4eg46u@4ax.com>
Date: Mon, 20 Jan 2003 18:10:24 +0100 (CET)
Subject: Re: performance question
From: "Moritz Lennert" <mlennert@club.worldonline.be>
To: "Manfred Koizar" <mkoi-pg@aon.at>
Cc: pgsql-sql@postgresql.org
Reply-To: mlennert@club.worldonline.be
X-Mailer: SquirrelMail (version 1.4.0 RC1)
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
X-Priority: 3
Importance: Normal
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/300
X-Sequence-Number: 11165

> On Mon, 20 Jan 2003 16:42:12 +0100 (CET), "Moritz Lennert"
> <mlennert@club.worldonline.be> wrote:
>>explain analyze select commune_residence, type_diplome from rec81 where
>>type_diplome = '11';
>>NOTICE:  QUERY PLAN:
>>
>>Seq Scan on rec81  (cost=0.00..120316.30 rows=177698 width=15) (actual
>>time=23.03..219164.82 rows=176621 loops=1)
>>Total runtime: 226149.03 msec
>
> Assumung that there are no NULLs, I guesstimate that 25 tuples should
> fit onto one page (could be more than 40 if you use smallint and
> "char").  That gives 88K pages for 2.2M tuples.  However,
> cost=0.00..120316.30  tells us that there are almost 120K pages.  Time
> to do a VACUUM FULL?
>

I'll try that, although I haven't changed any of the tuples since import
of the data (this is a static table...)

> From what I've seen I think that the planner is right to choose a seq
> scan.  226 seconds for reading 120K pages (~ 1GB) is not very
> impressive, though.  What kind of disk do you have?

IDE, Samsung, 7200rpm

>  Is your disk heavily fragmented?

It shouldn't be.

> Did you enable DMA?

No, should I ?

>  What else was running on
> your machine while you did that VACUUM ANALYZE?

Mozilla, maybe xterm with vi...

>>shared_buffers = 128
>
> It won't help much for this query we are discussing, but I would
> recommend setting shared_buffers to something in the range [1000,
> 4000].
>
> And one of my favorites: effective_cache_size = 40000
>

I will have to increase /proc/sys/kernel/shmmax for that, or ?

Thanks again !

Moritz