Re: Bug #474: Index using problem

1. I created a small database consisting of two connected tables:

create table table1 (
index1 int4 not null,
textfield varchar(1000) not null,
constraint PK_table1 primary key (index1)
);

create table table2 (
index2 int4 not null,
index1 int4 not null,
textfield varchar(1000) not null,
constraint PK_table2 primary key (index2),
constraint FK_table1 foreign key (index1)
references table1 (index1)
on delete restrict on update restrict
);

2. Then I insert 100 rows ($n=1..100) in each of these tables:

insert into table1 VALUES ($n, '123456789');
insert into table2 VALUES ($n, $n, '123456789');

3. then I send a "vacuum analyze" and an "explain select * from table1 where index1 = 543;"
The output is:
NOTICE: QUERY PLAN:
Index Scan using pk_table1 on table1 (cost=0.00..2.01 rows=1 width=16)

4. Then I insert 4900 rows into each of these tables like in step 2.

----------------------------

-- Test A: --
Then I send a "vacuum analyze;"
and "delete from table2;",
and "delete from table1;"
and rerun steps 2 to 4 -> step 4 takes 39 seconds.

-- Test B: --
Then I send "delete from table2;",
and "delete from table1;",
and a "vacuum analyze;"
and rerun steps 2 to 4 -> step 4 takes 81 seconds.

Although the "explain" command tells me that an Index Scan is used, step 4 is much slower in Test B.
For both tests (steps 2-4) I use one connection to the database.
If I quit the connection after step 3 and establish a new connection for step 4 it takes 39 seconds in either cases.
-> Using one connection the optimizer for pk/fk-checking is not updated by a "vacuum analyze".

On Fri, 05 Oct 2001 09:52:20 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andreas Wernitznig <andreas(at)insilico(dot)com> writes:
> > If I don't disconnect from the database but use the first connection
> > again, the database still uses the (now) slower "seq scan" for
> > "primary/foreign key" checking. In this case the query optimizer
> > statistics are not updated for established connections.
>
> Sure they are --- in my tests, anyway. What did you do *exactly*?
>
> regards, tom lane
>