Re: Followup - expression (functional) index use in joins

Roger Ging <rging(at)paccomsys(dot)com> writes:
> See results below.

Thanks for the report. It seems the issue is that the estimate for the
number of matching rows is way off (870 vs 8):

> -> Index Scan using idx_program_mri_id_no_program on program p
> (cost=0.00..3400.74 rows=870 width=40) (actual time=0.041..0.127 rows=8
> loops=32)

which discourages the planner from using a nestloop. I'm not sure we
can do much about this in the short term. There's been some discussion
of keeping statistics about the values of functional indexes, which
would allow a better estimate to be made in this situation; but that
won't happen before 7.5 at the earliest.

> Turning enable_hashjoin off made the query run as it had on v7.3. We
> have worked around this by changing the index from a function call to a
> direct index on a new column with the results of the function maintained
> by a trigger. Would there be performance issues from leaving
> enable_hashjoin off, or do you recomend enabling it, and working around
> function calls in indices?

Turning enable_hashjoin off globally would be a *really bad* idea IMHO.
The workaround with a derived column seems okay, though certainly a pain
in the neck. Can you manage to turn off enable_hashjoin just for this
one query? That might be the best short-term workaround.

regards, tom lane