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