[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