Re: Use of index in 7.0 vs 6.5

Ryan Bradetich <ryan_bradetich(at)hp(dot)com> writes:
> I am in the process of transitioning from postgreSQL 6.5.3 to
> postgreSQL 7.0. I ran into an issue where a sequential scan
> is being choosen on postgreSQL 7.0 where an index scan was
> choosen on postgreSQL 6.5.3.

Since you're complaining, I assume the seqscan is slower ;-).
But you didn't say how much slower --- what are the actual timings?

Basically what's going on here is that we need to tune the fudge-factor
constants in the cost model so that they have something to do with
reality on as wide a variety of systems as possible. You did an
excellent job of showing the estimates the planner computed --- but
what we really need here is to see how those relate to reality.

> I do not understand why the planner would choose a seqscan over the
> index scan because 6704/4,630,229 is ~ 0.15%.

I'm a bit surprised too. What is the average tuple width on this table?
(Actually, probably a better question is how many pages and tuples
are in the relation according to its pg_class entry. Try "select * from
pgclass where relname = 'medusa'".)

regards, tom lane