Re: indexed function performance

"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