BUG #14438: Wrong row count in the join plan with unique index scan

The following bug has been logged on the website:

Bug reference: 14438
Logged by: Nikolay Nikitin
Email address: nikolay(dot)nikitin(at)infowatch(dot)com
PostgreSQL version: 9.6.1
Operating system: Red Hat server 6.7
Description:

Hi,

I execute select with "in" condition on the empty table.
This select works with wrong plan.
I think the wrong plan is chosen becose the postgres optimizer incorrectly
calculates row count in the right plan.
Sample:

drop table if exists test_main;

-- main table with many data rows
create table test_main
(
id numeric(20) not null,
tms timestamp not null,
primary key (id)
);

create index idx_test_main_tms on test_main(tms desc);

insert into test_main(id, tms)
select a, current_date + (trunc(random() * 3 * 24 * 60) || '
minutes')::interval
from generate_series(1, 100000) a;

analyze test_main;

drop table if exists test_slave;

-- empty table test_slave
create table test_slave
(
id numeric(20) not null,
n numeric
);

create index idx_test_slave_n on test_slave(n);

analyze test_slave;

-- Wrong plan. At first idx_test_main_tms index is used.
explain
select *
from test_main
where tms between current_date and current_date + 1
and id in
(select id from test_slave where n = 5)
order by tms desc
limit 100;

-- Right plan with error. At first idx_test_slave_n index is used.
explain
select *
from test_main
where tms between current_date and current_date + '1 hour'::interval
and id in
(select id from test_slave where n = 5)
order by tms desc
limit 100;

The wrong plan goes from the big main table.
The right plan scans the slave empty table at the beginning.
But it has error:
"Limit (cost=56.82..57.07 rows=100 width=14)"
" -> Sort (cost=56.82..58.61 rows=718 width=14)"
" Sort Key: test_main.tms"
" -> Nested Loop (cost=12.97..29.37 rows=718 width=14)"
" -> HashAggregate (cost=12.67..12.69 rows=2 width=20)"
" Group Key: test_slave.id"
" -> Bitmap Heap Scan on test_slave (cost=4.19..12.66
rows=5 width=20)"
" Recheck Cond: (n = 5::numeric)"
" -> Bitmap Index Scan on idx_test_slave_n
(cost=0.00..4.19 rows=5 width=0)"
" Index Cond: (n = 5::numeric)"
" -> Index Scan using test_main_pkey on test_main
(cost=0.29..8.33 rows=1 width=14)"
" Index Cond: (id = test_slave.id)"
" Filter: ((tms >= ('now'::cstring)::date) AND (tms <=
(('now'::cstring)::date + '01:00:00'::interval)))"

The HashAggregate step returns 2 rows. It joins with the main table by using
primary unique index test_main_pkey.
The Nested Loop step has to return maximum 2 rows but we see rows=718. It is
incorrect calculation.
And therefore the Sort step is so long.
And on greater timestamp interval the right plan cost will be more the wrong
plan cost.

Also it is very strange that the record count is equal 5, but not 0 in step
(Bitmap Index Scan on idx_test_slave_n (cost=0.00..4.19 rows=5 width=0)).
Slave table is empty, but row count is equal 5.

Regards,
Nikolay.