Incorrect expected rows by ANALYZE

From: Kenji Sugita <sugita(at)srapc1327(dot)sra(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Incorrect expected rows by ANALYZE
Date: 2003-04-07 08:35:36
Message-ID: 20030407.173536.74735322.sugita@sra.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Assuming that following queries are executed on PostgreSQL 7.3.

create table c1 (i integer);
create table c2 (i integer);
insert into c1 values (1 .. 100000);
insert into c2 values (1 .. 100000);
(A) delete from c1;
(B) insert into c1 values (1 .. 100000);
analyze;
select * from c1, c2 where c1.i = c2.i;

=# explain analyze select * from c1, c2 where c1.i = c2.i;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Hash Join (cost=950.90..3713.21 rows=5512 width=8) (actual time=1381.88..26452.33 rows=100000 loops=1)
Hash Cond: ("outer".i = "inner".i)
-> Seq Scan on c2 (cost=0.00..1442.07 rows=100107 width=4) (actual time=0.28..1038.02 rows=100000 loops=1)
-> Hash (cost=937.12..937.12 rows=5512 width=4) (actual time=1381.29..1381.29 rows=0 loops=1)
-> Seq Scan on c1 (cost=0.00..937.12 rows=5512 width=4) (actual time=76.83..858.99 rows=100000 loops=1)
Total runtime: 26600.80 msec
(6 rows)

=#

Expected rows of 'Seq Scan on c1' does not coincide with actual rows. These
two values coincide after vacuum and expected rows coincide with actual rows
when queries (A) and (B) are not executed.

=# explain analyze select * from c1, c2 where c1.i = c2.i;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Hash Join (cost=2035.34..7259.35 rows=100107 width=8) (actual time=1367.11..8604.49 rows=100000 loops=1)
Hash Cond: ("outer".i = "inner".i)
-> Seq Scan on c1 (cost=0.00..1442.07 rows=100107 width=4) (actual time=0.28..556.03 rows=100000 loops=1)
-> Hash (cost=1442.07..1442.07 rows=100107 width=4) (actual time=1365.79..1365.79 rows=0 loops=1)
-> Seq Scan on c2 (cost=0.00..1442.07 rows=100107 width=4) (actual time=0.30..626.40 rows=100000 loops=1)
Total runtime: 9113.14 msec
(6 rows)

=#

Why do dead tuples cause incorrect expected rows by ANALYZE?

Kenji Sugita

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Hannu Krosing 2003-04-07 08:58:14 Re: contrib and licensing
Previous Message Michael Paesold 2003-04-07 07:01:52 Re: contrib and licensing