From: Tom Lane [...@sss.pgh.pa.us] Subject: Re: [HACKERS] Wrong stats for empty tables
"Emmanuel Cecchet" <emmanuel.cecc...@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) Sort Key: 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 on child3 father (cost=0.00..13.10 rows=310 width=226) -> Seq Scan on child4 father (cost=0.00..13.10 rows=310 width=226) -> Seq Scan on child5 father (cost=0.00..13.10 rows=310 width=226) -> Seq Scan on child6 father (cost=0.00..13.10 rows=310 width=226) -> Seq Scan on child7 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) (16 rows) I must admit that I did not see what the original intention was to get this behavior. Emmanuel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers