Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> You're probably ending up with different plans since in one case it has
> a plain column reference and in the other it has a marginally complicated
> expression in the join condition.
Yeah. 7.3 and before cannot do merge or hash joins on conditions that
are any more complex than "var = var". The query with the trunc() is
undoubtedly falling back to the stupidest kind of nestloop.
> As something to try, perhaps make a function that returns
> trunc($1/100000.0)*100000 and index on that function for the column and
> see if that changes the plan you get.
It might help --- you might possibly get a nestloop-with-inner-indexscan
out of that. Not sure though, since the planner is likely to be using
bad guesstimates about the selectivity of the expression.
7.4 should do better on this.
regards, tom lane