Re: Wrong stats for empty tables

From: Tom Lane [tgl@sss.pgh.pa.us]
Subject: Re: [HACKERS] Wrong stats for empty tables

"Emmanuel  Cecchet" <Emmanuel.Cecchet@asterdata.com> writes:
> Is this a bug?

No, it's intentional.


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;
                   QUERY PLAN 
------------------------------------------------------------------------Sort  (cost=37.81..37.82 rows=5 width=230)
SortKey: father.id  ->  Hash Join  (cost=23.44..37.75 rows=5 width=230)        Hash Cond: (father.id = other.id1)
->  Seq Scan on father  (cost=0.00..13.10 rows=310 width=226)        ->  Hash  (cost=23.38..23.38 rows=5 width=8)
      ->  Seq Scan on other  (cost=0.00..23.38 rows=5 width=8)                    Filter: (id2 = 2) 
(8 rows)

manu=# create table child1() inherits(father);
manu=# create table child2() inherits(father);
manu=# create table child3() inherits(father);
manu=# create table child4() inherits(father);
manu=# create table child5() inherits(father);
manu=# create table child6() inherits(father);
manu=# create table child7() inherits(father);
manu=# create index i1 on child1(id);
manu=# create index i2 on child2(id);
manu=# create index i3 on child3(id);
manu=# create index i4 on child4(id);
manu=# create index i5 on child5(id);
manu=# create index i6 on child6(id);
manu=# create index i7 on child7(id);
manu=# explain select father.*,id2 from father left join other on father.id=other.id1 where id2=2 order by id;
                         QUERY PLAN 
------------------------------------------------------------------------------------Sort  (cost=140.00..140.16 rows=62
width=230) Sort Key: public.father.id  ->  Hash Join  (cost=23.44..138.16 rows=62 width=230)        Hash Cond:
(public.father.id= other.id1)        ->  Append  (cost=0.00..104.80 rows=2480 width=226)              ->  Seq Scan on
father (cost=0.00..13.10 rows=310 width=226)              ->  Seq Scan on child1 father  (cost=0.00..13.10 rows=310
width=226)             ->  Seq Scan on child2 father  (cost=0.00..13.10 rows=310 width=226)              ->  Seq Scan
onchild3 father  (cost=0.00..13.10 rows=310 width=226)              ->  Seq Scan on child4 father  (cost=0.00..13.10
rows=310width=226)              ->  Seq Scan on child5 father  (cost=0.00..13.10 rows=310 width=226)              ->
SeqScan on child6 father  (cost=0.00..13.10 rows=310 width=226)              ->  Seq Scan on child7 father
(cost=0.00..13.10rows=310 width=226)        ->  Hash  (cost=23.38..23.38 rows=5 width=8)              ->  Seq Scan on
other (cost=0.00..23.38 rows=5 width=8)                    Filter: (id2 = 2) 
(16 rows)



I must admit that I did not see what the original intention was to get this behavior.
Emmanuel