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                                      


Mon, 07 Apr 2003 17:35:36 +0900 (JST)
Kenji Sugita <sugita@srapc1327.sra.co.jp>
Incorrect expected rows by ANALYZE
Kenji Sugita <sugita@srapc1327.sra.co.jp> writes:
> Why do dead tuples cause incorrect expected rows by ANALYZE?

Because it extrapolates the density of live rows in the whole table from
their density in the first few pages.

There has been some previous discussion of how to improve this, but no
really workable ideas AFAIR.  Ultimately, unless you want to abandon
the notion that ANALYZE only samples the table and doesn't read the
whole thing, there will be situations where it gets fooled.  But
possibly we can make those situations less likely to correspond to
common real-world cases.

			regards, tom lane


Mon, 07 Apr 2003 10:19:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Incorrect expected rows by ANALYZE 
> Kenji Sugita <sugita@srapc1327.sra.co.jp> writes:
>> Why do dead tuples cause incorrect expected rows by ANALYZE?
> 
> Because it extrapolates the density of live rows in the whole table
> from their density in the first few pages.
> 
> There has been some previous discussion of how to improve this, but no
> really workable ideas AFAIR.  Ultimately, unless you want to abandon
> the notion that ANALYZE only samples the table and doesn't read the
> whole thing, there will be situations where it gets fooled.  But
> possibly we can make those situations less likely to correspond to
> common real-world cases.

Couldn't there be an ANALYZE FULL option?


Mon, 7 Apr 2003 11:21:27 -0400 (EDT)
pgsql@mohawksoft.com
Re: Incorrect expected rows by ANALYZE
On Mon, 7 Apr 2003 pgsql@mohawksoft.com wrote:

> > Kenji Sugita <sugita@srapc1327.sra.co.jp> writes:
> >> Why do dead tuples cause incorrect expected rows by ANALYZE?
> > 
> > Because it extrapolates the density of live rows in the whole table
> > from their density in the first few pages.
> > 
> > There has been some previous discussion of how to improve this, but no
> > really workable ideas AFAIR.  Ultimately, unless you want to abandon
> > the notion that ANALYZE only samples the table and doesn't read the
> > whole thing, there will be situations where it gets fooled.  But
> > possibly we can make those situations less likely to correspond to
> > common real-world cases.
> 
> Couldn't there be an ANALYZE FULL option?

There's something close, that is, you can change the statistics target for 
a column in a table like so:

ALTER TABLE [ ONLY ] table [ * ]
    ALTER [ COLUMN ] column SET STATISTICS integer

which can make it so that more of the rows are analyzed.


Mon, 7 Apr 2003 09:32:35 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: Incorrect expected rows by ANALYZE
On Mon, 7 Apr 2003 pgsql@mohawksoft.com wrote:
>> Couldn't there be an ANALYZE FULL option?

It already gets the row count right if you did VACUUM ANALYZE.  I don't
think we need ANALYZE FULL (at least, not for this purpose).

"scott.marlowe" <scott.marlowe@ihs.com> writes:
> There's something close, that is, you can change the statistics target for 
> a column in a table like so:
> ALTER TABLE [ ONLY ] table [ * ]
>     ALTER [ COLUMN ] column SET STATISTICS integer
> which can make it so that more of the rows are analyzed.

I'm not sure though how effective that will be at avoiding the problem
Kenji is complaining of.

			regards, tom lane


Mon, 07 Apr 2003 11:39:59 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Incorrect expected rows by ANALYZE 
Tom Lane wrote:
> Kenji Sugita <sugita@srapc1327.sra.co.jp> writes:
> > Why do dead tuples cause incorrect expected rows by ANALYZE?
> 
> Because it extrapolates the density of live rows in the whole table from
> their density in the first few pages.

Shouldn't it select random pages within the table for sampling?  It
would be almost as fast and would probably yield much more reliable
statistics, because it would be much more likely to reflect
recently-made changes, particularly on tables that haven't been
VACUUMed recently..


-- 
Kevin Brown					      kevin@sysexperts.com


Mon, 7 Apr 2003 13:00:22 -0700
Kevin Brown <kevin@sysexperts.com>
Re: Incorrect expected rows by ANALYZE