Re: index on numbers not honoured

On Fri, 16 Nov 2001, Risko Peter wrote:

> On Thu, 15 Nov 2001, Ilker Egilmez wrote:
> > hi risko,
> > try to create your table, then create your index and then insert your values.
> > this time the index will be used - very strange!
> > ilker -)
> I summed it:
>
> show enable_seqscan;
> show enable_indexscan;
> drop index i;
> drop table test;
> create table test(a int4);
> create index i on test(a);
> copy test from stdin;
> 65
> 87
> 23
> \.
> explain select * from test where a=87;
> drop index i;
> explain select * from test where a=87;
> create index i on test (a);
> explain select * from test where a=87;
>
> I experienced it too, first indexed, but then seqscan and seqscan again.
> I think it is because the query optimizer. I advise you to control
> manually in these cases the behavior of it by setting the enable_seqscan
> variable (I often do it :).

Well, you're going to need a vacuum analyze in there between the copy
and first select to get something meaningful. The reason it probably uses
an index on the first one is that the default statistics will choose that.
However, that is almost certainly the *wrong* plan for all three of those
queries. Choosing from a table that's one page should almost certainly
use sequence scan and choosing one row of three should in most cases
as well (see past discussions for details)