Re: Inaccurate row count estimation

This is 8.3.0.

Here is the reproduce code:

create table contacts (
cid integer primary key,
pid integer not null,
cpid integer
);

create index ix_contacts_pid on contacts (pid);
create index ix_contacts_cpid on contacts (cpid);

create table pinfo (
pid integer,
constraint pk_pinfo primary key (pid)
);

create table pinfo_p00 (
constraint pk_pinfo_p00 primary key (pid),
constraint cc_pinfo_p00_pid check(pid > 0 and pid < 100000)
) inherits (pinfo);

create table pinfo_p01 (
constraint pk_pinfo_p01 primary key (pid),
constraint cc_pinfo_p01_pid check(pid >= 100000 and pid < 200000)
) inherits (pinfo);

insert into pinfo_p00 (pid)
select i from generate_series(10, 10000) i;

insert into pinfo_p01 (pid)
select i from generate_series(100010, 110000) i;

create sequence contacts_seq start 100;

insert into contacts (cid, pid, cpid)
select nextval('contacts_seq'), p, cp.pid
from generate_series(100, 1000) p,
(
(select pid from pinfo_p00 order by random() limit 20)
union all
(select pid from pinfo_p01 order by random() limit 20)
) cp

analyze contacts;
analyze pinfo;
analyze pinfo_p00;
analyze pinfo_p01;

explain analyze
select *
from contacts c
left join pinfo pi on (pi.pid = c.cpid)
where c.pid = 200 ;

QUERY PLAN
Nested Loop Left Join (cost=4.56..569.17 rows=4364 width=16) (actual
time=0.150..4.595 rows=40 loops=1)
Join Filter: (pi.pid = c.cpid)
-> Bitmap Heap Scan on contacts c (cost=4.56..100.34 rows=39 width=12)
(actual time=0.067..0.441 rows=40 loops=1)
Recheck Cond: (pid = 200)
-> Bitmap Index Scan on ix_contacts_pid (cost=0.00..4.55 rows=39
width=0) (actual time=0.041..0.041 rows=40 loops=1)
Index Cond: (pid = 200)
-> Append (cost=0.00..11.98 rows=3 width=4) (actual time=0.048..0.076
rows=1 loops=40)
-> Index Scan using pk_pinfo on pinfo pi (cost=0.00..1.40 rows=1
width=4) (actual time=0.008..0.008 rows=0 loops=40)
Index Cond: (pi.pid = c.cpid)
-> Index Scan using pk_pinfo_p00 on pinfo_p00 pi (cost=0.00..5.29
rows=1 width=4) (actual time=0.012..0.015 rows=0 loops=40)
Index Cond: (pi.pid = c.cpid)
-> Index Scan using pk_pinfo_p01 on pinfo_p01 pi (cost=0.00..5.29
rows=1 width=4) (actual time=0.011..0.015 rows=0 loops=40)
Index Cond: (pi.pid = c.cpid)
Total runtime: 4.941 ms