From: Moritz Lennert <mlennert@club.worldonline.be>
To: Manfred Koizar <mkoi-pg@aon.at>
Cc: pgsql-sql@postgresql.org
Subject: Re: performance question
Date: Mon, 20 Jan 2003 18:10:24 +0100 (CET)
Message-ID: <44591.164.15.128.4.1043082624.squirrel@http://moritz.homelinux.org> (raw)
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>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mlennert@club.worldonline.be, mkoi-pg@aon.at
Subject: Re: performance question
In-Reply-To: <44591.164.15.128.4.1043082624.squirrel@http://moritz.homelinux.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox