[BUGS] BUG #14664: Nonsensical join selectivity estimation despite n_distinct

The following bug has been logged on the website:

Bug reference:      14664
Logged by:          Marko Tiikkaja
Email address:      marko@joh.to
PostgreSQL version: 9.6.3
Operating system:   Linux, OS X
Description:

=# create unlogged table qqq(pk int, other int);
CREATE TABLE

=# insert into qqq select a, a / 10 from generate_series(1, (10^8)::int)
a;
INSERT 0 100000000

=# alter table qqq add primary key(pk);
ALTER TABLE

=# create index on qqq(other);
CREATE INDEX

=# alter table qqq alter column other set (n_distinct = -0.1);
ALTER TABLE

=# analyze qqq;
ANALYZE

=# explain select * from qqq q1 join qqq q2 using (other) where q1.pk = 1;                                   QUERY
PLAN
-----------------------------------------------------------------------------------Nested Loop  (cost=1.14..17.43
rows=58width=12)  ->  Index Scan using qqq_pkey on qqq q1  (cost=0.57..8.59 rows=1 
width=8)        Index Cond: (pk = 1)  ->  Index Scan using qqq_other_idx on qqq q2  (cost=0.57..8.74 rows=10
width=8)        Index Cond: (other = q1.other)
(5 rows)

Despite the fact that postgres knows that the lookup on q2 will always
produce exactly 10 rows, it still estimates it at 58.  (The problem exists
even without n_distinct, but in that case you could argue that the lookup on
q2 is just an estimate and doing normal join selectivity estimation makes
sense.)


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs