Re: Use of index in 7.0 vs 6.5

Tom Lane wrote:

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

Opps... Had them written down, just forgot to include them in the email :)

with enable_seqscan = on:
real 18.05
sys 0.01
user 0.02

with enable_seqscan = off:
real 0.08
sys 0.01
user 0.02

I stopped and restarted the postmaster daemon between these timing to
flush the cache.

> 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

procman=# select * from pg_class where relname = 'medusa';
relname | reltype | relowner | relam | relpages | reltuples | rellongrelid
| relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers
| relukeys | relfkeys | relrefs | relhas
pkey | relhasrules | relacl
---------+---------+----------+-------+----------+-----------+--------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+-------

-----+-------------+--------
medusa | 0 | 36000 | 0 | 120076 | 4630229 | 0
| t | f | r | 6 | 0 | 0
| 0 | 0 | 0 | f
| f |
(1 row)

procman=# \d medusa
Table "medusa"
Attribute | Type | Modifier
-----------+-----------+----------
host_id | integer |
timestamp | timestamp |
current | integer |
catagory | text |
cat_desc | text |
anomaly | text |

This table has two fairly large text fields, the cat_desc and the anomaly.
The catagory field is very short and in the format: [ABC][0-9][0-9].

Thanks for the help,

- Ryan