I'm looking for some suggestions on database tuning. I've looked on
the postgres site and google and found very little in-depth information
on tuning.
I have a database (7.2.2) that gets a lot of read access and very few
updates/inserts. The server (rh73 dual xeon 2ghz) running this database
has 2gb of memory and is only running postgres. The database itself is
about 1.5gb (via du -sk), with a lot of that 1.5gb not being part of the
active dataset. We find that there is almost no IO on this machine.
The small amount of I/O is because of the infrequent writes and the
aggressive disk caching of the linux kernel.
It seems the planner tries to avoid I/O so much that the default tuning
parameters works against us a bit. i've tried a few changes here and
there, but without much luck since i don't really know what to change
tho values to.
One of the things I see over and over again is the planner picking a seq
scan over an index scan. And practically always, when I force a index
scan and use explain analyze the index scan would have been faster.
I've heard the explanation be that at some point it's cheaper to do a
scan instead of using the index. I think that assumption might be based
on IO estimates.
I can just give one example here that's indicative of what I'm seeing
over and over. The two explain outputs are below, and both are
executing without any I/O. The table has 12904 rows, the plan estimates
959 rows (about 7.4% of table) and actually only 639 (~ 5%) are
fetched. The table scan consistently takes 50 times longer to execute.
I see this over and over and over. I know a few hundred msec here and
there seems small, but this machine is performing at least a few million
queries a day -- it adds up.
Any advice or pointers would be greatly appreciated.
--thanks
basement=# explain analyze select count(*) from game_cover where
cover_scan_of_id = 6;
NOTICE: QUERY PLAN:
Aggregate (cost=1970.70..1970.70 rows=1 width=0) (actual
time=121.07..121.07 rows=1 loops=1)
-> Seq Scan on game_cover (cost=0.00..1968.30 rows=959 width=0)
(actual time=0.13..120.56 rows=639 loops=1)
Total runtime: 121.14 msec
and
basement=# set enable_seqscan TO false;
SET VARIABLE
basement=# explain analyze select count(*) from game_cover where
cover_scan_of_id = 6;
NOTICE: QUERY PLAN:
Aggregate (cost=2490.66..2490.66 rows=1 width=0) (actual
time=2.45..2.45 rows=1 loops=1)
-> Index Scan using game_cover_scan_of on game_cover
(cost=0.00..2488.26 rows=959 width=0) (actual time=0.12..2.03 rows=639
loops=1)
Total runtime: 2.54 msec
