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
-------------------------------------------------------------------------------------------------------------------
HashJoin (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=100000loops=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
---------------------------------------------------------------------------------------------------------------------
HashJoin (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.03rows=100000 loops=1) -> Hash (cost=1442.07..1442.07 rows=100107 width=4) (actual
time=1365.79..1365.79rows=0 loops=1) -> Seq Scan on c2 (cost=0.00..1442.07 rows=100107 width=4) (actual
time=0.30..626.40rows=100000 loops=1) Total runtime: 9113.14 msec (6 rows)
=#
Why do dead tuples cause incorrect expected rows by ANALYZE?
Kenji Sugita