Re: empty table explain...

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