Message-Id: <200201101203.OAA00420@dcave.digsys.bg>
To: "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
cc: "Tom Lane" <tgl@sss.pgh.pa.us>, pgsql-hackers@postgresql.org
Subject: Re: again on index usage 
In-reply-to: Your message of "Thu, 10 Jan 2002 11:55:20 +0100."
	<46C15C39FEB2C44BA555E356FBCD6FA41EB499@m0114.s-mxs.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Date: Thu, 10 Jan 2002 14:03:15 +0200
From: Daniel Kalchev <daniel@digsys.bg>

[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