Thread

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

Andriy I Pilipenko <bamby@marka.net.ua> — 1999-03-18T15:22:14Z

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