Re: indexed function performance

"mikelin" <mikelin(dot)ca(at)gmail(dot)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