Wrong stats for empty tables - Mailing list pgsql-hackers

Hi,

Here is an example showing the problem:

Welcome to psql 8.3.6, the PostgreSQL interactive terminal.
manu=# create table foo (x int);
CREATE TABLE
manu=# explain select * from foo;                     QUERY PLAN
-------------------------------------------------------Seq Scan on foo  (cost=0.00..34.00 rows=2400 width=4)
(1 row)

manu=# analyze foo;
ANALYZE
manu=# explain select * from foo;                     QUERY PLAN
-------------------------------------------------------Seq Scan on foo  (cost=0.00..34.00 rows=2400 width=4)
(1 row)

manu=# insert into foo values (1);
INSERT 0 1
manu=# analyze foo;
ANALYZE
manu=# explain select * from foo;                   QUERY PLAN
---------------------------------------------------Seq Scan on foo  (cost=0.00..1.01 rows=1 width=4)
(1 row)


Now a possible cause for this might be the relpages attribute in pg_class (the default value 0 does not seem to be
interpretedcorrectly): 

manu=# create table bar(x int);
CREATE TABLE
manu=# explain select * from bar;                     QUERY PLAN
-------------------------------------------------------Seq Scan on bar  (cost=0.00..34.00 rows=2400 width=4)
(1 row)

manu=# select relpages from pg_class where relname='bar';relpages
----------       0
(1 row)
manu=# update pg_class set relpages=1 where relname='bar';
UPDATE 1
manu=# explain select * from bar;                   QUERY PLAN
---------------------------------------------------Seq Scan on bar  (cost=0.00..0.00 rows=1 width=4)
(1 row)


This is a real problem if you have a lot of empty child tables. Postgres will not optimize correctly queries in the
presenceof empty child tables. 

Is this a bug?

Thanks for your help,
Emmanuel
--
Emmanuel Cecchet
Aster Data Systems
Web: http://www.asterdata.com