Re: Wrong stats for empty tables

On Tue, May 5, 2009 at 2:03 PM, Emmanuel  Cecchet
<Emmanuel.Cecchet@asterdata.com> wrote:
> So what is the rationale behind not being able to use indexes and optimizing empty tables as in the following
example:
>
> manu=# create table father (id int, val int, tex varchar(100), primary key(id));
> manu=# create table other (id1 int, id2 int, data varchar(10), primary key(id1,id2));
> insert some data
> manu=# explain select father.*,id2 from father left join other on father.id=other.id1 where id2=2 order by id;

Just because the table was empty at the time statistics were most
recently gathered doesn't mean it's still empty at the time the query
is executed.

ANALYZE;
PREPARE foo AS SELECT ...;
INSERT INTO ...some previously empty child table...
EXECUTE foo;

In order to rely on this for query planning, you'd need some way to
invalidate any cached plans when inserting into an empty table.

...Robert