Re: [BUGS] General Bug Report: Bug in optimizer

On Thu, 18 Mar 1999, Vadim Mikheev wrote:

> Unprivileged user wrote:
> >
> > PostgreSQL version : 6.4.2
> >
> ...
> >
> > Here is an example session. Note that in first SELECT
> > backend uses index scan, and in second one it uses
> > sequental scan.
> >
> > == cut ==
> > bamby=> create table table1 (field1 int);
> > CREATE
> > bamby=> create index i_table1__field1 on table1 (field1);
> > CREATE
> > bamby=> explain select * from table1 where field1 = 1;
> > NOTICE: QUERY PLAN:
> >
> > Index Scan using i_table1__field1 on table1 (cost=0.00 size=0 width=4)
> ^^^^^^
> Hmmm... Seems that vacuum wasn't run for table1.
> Why is index used ?!!!
> It's bug!

Why I need to vacuum immediately after creating table?

Here is another example from live system:

== cut ==

statserv=> select count(*) from ctime;
count
-----
94256
(1 row)

statserv=> explain select * from ctime where ctg=-1;
NOTICE: QUERY PLAN:

Seq Scan on ctime (cost=3646.86 size=8412 width=54)

EXPLAIN
statserv=> explain select * from ctime where ctg=1;
NOTICE: QUERY PLAN:

Index Scan using i_ctime__ctg on ctime (cost=2.05 size=2 width=54)

EXPLAIN

== cut ==

>
> > EXPLAIN
> > bamby=> explain select * from table1 where field1 = -1;
> > NOTICE: QUERY PLAN:
> >
> > Seq Scan on table1 (cost=0.00 size=0 width=4)
>
> Run
>
> vacuum table1

Did it. Doesn't help.

Andriy I Pilipenko
PAI1-RIPE