Roger Ging <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
