From: Daniel Kalchev <daniel@digsys.bg>
To: Zeugswetter Andreas SB SD <ZeugswetterA@spardat.at>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: again on index usage
Date: Thu, 10 Jan 2002 14:03:15 +0200
Message-ID: <200201101203.OAA00420@dcave.digsys.bg> (raw)
In-Reply-To: <46C15C39FEB2C44BA555E356FBCD6FA41EB499@m0114.s-mxs.net>
[with the new effective_cache_size = 6400]
explain
SELECT sum(input), sum(output) FROM iplog_gate200112
WHERE
'2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2001-12-02 00:00:00+02' AND
'2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2002-01-01 00:00:00+02' AND
ipaddr <<= '193.68.240.0/20' AND 'uni-gw' ~ router;
gives
Aggregate (cost=56111.97..56111.97 rows=1 width=16)
-> Seq Scan on iplog_gate200112 (cost=0.00..56110.54 rows=284 width=16)
takes 3 min to execute. (was 10 sec after fresh restart)
db=# set enable_seqscan to off;
Aggregate (cost=84980.10..84980.10 rows=1 width=16)
-> Index Scan using iplog_gate200112_ipdate_idx on iplog_gate200112
(cost=0.00..84978.68 rows=284 width=16)
takes 1.8 min to execute. (was 2 sec after fresh reshart)
Still proves my point, But the fresh restart performance is impressive. After
few minutes the database takes its normal load and in my opinion the buffer
cache is too much cluttered with pages from other tables.
Which brings another question: with so much RAM recent equipment runs, it may
be good idea to specifically add to INSTALL instruction on tuning the system
as soon as it is installed. Most people will stop there, especially after an
upgrade (as I did).
Daniel
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-hackers@postgresql.org
Cc: daniel@digsys.bg, ZeugswetterA@spardat.at, tgl@sss.pgh.pa.us
Subject: Re: again on index usage
In-Reply-To: <200201101203.OAA00420@dcave.digsys.bg>
* 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