Обсуждение: empty table explain...

Hi all,
I'm curious to know why, if a table is empty, it seems that an ANALYZE of the
table does not insert any stats in the pg_stats table, since maybe this could
be useful to solve joins including this table. Second, if I execute an EXPLAIN
on an empty table, even after an ANALYZE of the table, I got an explain that
proposes me a row numbers and size that I cannot understand (since it seems to
be different depending on the table definition).
As an example:

# create table test(id serial, descrizione character varying(20));

# explain  select * from test;
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on test  (cost=0.00..18.80 rows=880 width=62)

# analyze verbose test;
INFO: ?analyzing "public.test"
INFO: ?"test": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0
rows in sample, 0 estimated total rows
ANALYZE

# explain select * from test;
? ? ? ? ? ? ? ? ? ? ? ?QUERY PLAN
--------------------------------------------------------
?Seq Scan on test ?(cost=0.00..18.80 rows=880 width=62)
(1 row)

# select count(*) from test;
?count
-------
? ? ?0
(1 row)

I know it does not make sense having an empty table or worrying about, but I'm
just curious to know if there's a particular reason for the above behaviours.

Thanks,
Luca

On Mon, Oct 27, 2008 at 05:43:08PM +0100, Luca Ferrari wrote:
> I'm curious to know why, if a table is empty, it seems that an
> ANALYZE of the table does not insert any stats in the pg_stats table,
> since maybe this could be useful to solve joins including this table.

I think it's trying to be helpful under the assumption that people tend
to run ANALYSE (or it gets run automatically) between the table getting
created and lots of data being inserted.  A plan that's expecting zero
rows is going to do very bad things when it hits a table with millions
of rows, but the converse doesn't appear to be true.

> Second, if I execute an EXPLAIN on an empty table, even after an
> ANALYZE of the table, I got an explain that proposes me a row numbers
> and size that I cannot understand (since it seems to be different
> depending on the table definition).

I'm not too sure about this, but what it appears to do is when it
doesn't have any stats it assumes a few pages (10?) of space used, works
out how big each row is and calculates the number of rows from there.
How this is better than just assuming, say, 10000 rows I'm not sure; but
if it's to simplify the code then it doesn't seem like a bad assumption.


  Sam

Luca Ferrari <fluca1978@infinito.it> writes:
> I'm curious to know why, if a table is empty, it seems that an ANALYZE
> of the table does not insert any stats in the pg_stats table,

Uh ... because there are no stats to insert.  The only available
information is that the table is empty, which is something we track in
pg_class not pg_stats.

> Second, if I execute an EXPLAIN
> on an empty table, even after an ANALYZE of the table, I got an explain that
> proposes me a row numbers and size that I cannot understand (since it seems to
> be different depending on the table definition).

That's the planner not wanting to bet on an empty table staying empty.

            regards, tom lane