"mikelin" <mikelin.ca@gmail.com> writes:
> which sounds like caching, so I created an index on that function,
> expecting stellar performance, but the performance turned out to be
> pretty bad:
> words=# explain analyse select * from word order by
> word_difficulty(word) limit 100;
> I wouldn't have expected that Index Scan to be so slow.
The index scan actually is not slow; the problem is that the planner
neglects to suppress the computation of the sort-key columns in the
output rows, even though they're not referenced anywhere. Normally
that doesn't matter a whole lot, but if it's a really expensive function
then it does matter. We just noticed this problem a few weeks ago:
http://archives.postgresql.org/pgsql-performance/2006-11/msg00054.php
I'm hoping to fix this for 8.3, but suspect that the fix will be too
invasive to consider back-patching to older releases.
regards, tom lane