Re: Use of index in 7.0 vs 6.5 - Mailing list pgsql-sql

Tom Lane wrote:

> Ryan Bradetich <ryan_bradetich@hp.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