Re: RE: RE: Re: Dissapearing indexes, what's that all about?

I appriciate all the help i've gotten here...
anyway,
creating a table:

CREATE TABLE index_with (
id SERIAL,
name text
);

CREATE INDEX name_index ON index_with(name);

and filling it with 10000 rows made out of

$pwgen 8 10000 > data [enter]

and then running VACUUM and VACUUM ANALYZE
still yields a sequential scan doing a
select * from index_with where name > 'm';
namely
seq scan on index_with (cost=0.00..189 rows 5170 width=16)

Sorry to bother ýou guys this much.

Daniel Åkerud

> If I recall correctly, when the optimizer was changed
(greatly enhanced),
> there was a debate about what the default behavior should
be. The problem
> was that a large number of users would populate they're
database after
> index creation and see sluggishness because the
statistics had not yet been
> updated vs. the much smaller number of users that would
suffer at the hands
> of an index scan against a table that would be better
served with a
> sequential scan. I *think* the result of assuming 0 rows
in a newly created
> table, until the next vacuum, would yield a significant
increase in
> mailing-list traffic complaints to the tune of:
>
> "Why isn't PostgreSQL using my index?"
>
> followed by the usual
>
> "Did you run VACUUM ANALYZE?"
>
> So an assumption of 1000 rows was made, with 10 rows
matching your WHERE
> clause.
>
> Mike Mascari
> mascarm(at)mascari(dot)com
>
> -----Original Message-----
> From: Daniel ?erud [SMTP:zilch(at)home(dot)se]
> Sent: Sunday, April 01, 2001 12:43 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: RE: Re: [GENERAL] Dissapearing indexes,
what's that all about?
>
>
> after a refresh database the explain yields:
> index scan using xXxX (cost=0.00..8.14 rows=10 width=147)
> after a vacuum + vacuum analyze the explain yields:
> seq scan on acc xXxX A(cost=0.00..1.23 rows=1 width=147)
>
> humm, seems you are right here... but why is it choosing a
> index scan in the first place then?
>
> > What are the costs associated with the EXPLAIN output?
> Perhaps a sequential scan is *faster* then an index scan.
> >
> > Mike Mascari
> > mascarm(at)mascari(dot)com
>
>
> ---------------------------(end of broadcast)-------------
--------------
> TIP 2: you can get off all lists at once with the
unregister command
> (send "unregister YourEmailAddressHere" to
majordomo(at)postgresql(dot)org)
>