Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id XAA50774 for ; Wed, 24 May 2000 23:12:53 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id XAA19298; Wed, 24 May 2000 23:12:32 -0400 (EDT) To: Ryan Bradetich cc: pgsql-sql@postgreSQL.org Subject: Re: Use of index in 7.0 vs 6.5 In-reply-to: <392C6056.D5BF7FE8@hp.com> References: <392C6056.D5BF7FE8@hp.com> Comments: In-reply-to Ryan Bradetich message dated "Wed, 24 May 2000 17:05:58 -0600" Date: Wed, 24 May 2000 23:12:32 -0400 Message-ID: <19295.959224352@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200005/135 Ryan Bradetich 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