On Mon, Mar 2, 2015 at 2:19 PM, David Kubečka <kubecka....@gmail.com> wrote: > The question is why optimizer, or rather the cost estimator, produced so > much different estimates upon very small change in input. Moreover it seems > that the main culprit of bad estimates isn't actually directly related to > outer table, but rather to inner table. Just compare estimates for the two > index scans: > > With 'random_fk_dupl': > -> Index Scan using facts_fk_idx on facts (cost=0.42..5.75 > rows=100 width=15) (actual time=0.009..0.117 rows=98 loops=100) > With 'random_fk_uniq': > -> Index Scan using facts_fk_idx on facts (cost=0.42..214.26 > rows=100 width=15) (actual time=0.007..0.109 rows=98 loops=100)
Whoa. That's pretty dramatic. Am I correctly understanding that the two tables contain *exactly* the same data? Could the issue be that the optimizer thinks that one of the tables is thought to have a higher logical-to-physical correlation than the other (see pg_stats.correlation)? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
