Re: Strange query optimization in 7.3.2

Alec Mitchell <apm13(at)columbia(dot)edu> writes:
> I've encountered what seems to be a very strange behavior in the query
> optimizer using postgresql 7.3.2.

I think the reason for the change in plan is the same bug discussed at
http://fts.postgresql.org/db/mw/msg.html?mid=1064055

However, you will probably not like the fix, since it eliminates the
bogusly small cost estimate for the duplicated index condition, and
thereby ensures that your less-favored plan will always be chosen :-(

What would be interesting is to look into why the planner's estimated
costs are inaccurate. I think the main cause is the badly-off join
estimate for the tr/t join --- notice it's estimating 1119 rows out
where only 52 are actually produced. The nestloop's runtime is directly
proportional to the number of outer rows, so this leads directly to a
factor-of-20 overestimate of the nestloop's cost, discouraging the
planner from using it. The bug that's triggered by the duplicate
index condition underestimates the cost, thereby negating that error to
some extent.

You should look into whether increasing the statistics targets for
t.terminal and tr.terminal would improve the accuracy of the join
estimate.

regards, tom lane