Обсуждение: 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