Skip site navigation (1) Skip section navigation (2)

Re: Bug #474: Index using problem

From: Andreas Wernitznig <andreas(at)insilico(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Bug #474: Index using problem
Date: 2001-10-05 18:37:18
Message-ID: 20011005203718.247f5c7d.andreas@insilico.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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
> 

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-10-05 19:15:06
Subject: Re: Bug #474: Index using problem
Previous:From: Peter EisentrautDate: 2001-10-05 18:35:53
Subject: Re: Long options for psql in 7.1.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group