Re: [GENERAL] Inaccurate row count estimation

[Vyacheslav Kalinin]

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