Re: Performance Tuning Question

On Sun, Sep 08, 2002 at 11:04:31PM -0600, Brian Hirt wrote:
> 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.

Why is this a bad thing? The less IO the better, right?

> 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.

There are values somewhere to estimate the amount of cache to estimate for.
I beleive SHOW ALL will show all tunable parameters.

> 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.

Is there any clustering going on? Also, I'm assuming you have run VACUUM
ANALYZE over all the relevent tables. If possible, could you post the result
of:

select * from pg_stats where tablename = 'game_cover';

Hope this helps.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.