I'm trying to do a complicated ordering of a table with ~40k rows. I have an IMMUTABLE plpgsql function that returns an integer that I'll be sorting by, but the function is slow, so I want to cache it somehow. I found in the docs: "the index expressions are not recomputed during an indexed search, since they are already stored in the index." - http://www.postgresql.org/docs/8.1/static/indexes-expressional.html 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; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..90.57 rows=100 width=48) (actual time=43.718..3891.817 rows=100 loops=1) -> Index Scan using word_word_difficulty_idx on word (cost=0.00..37989.19 rows=41946 width=48) (actual time=43.711..3891.251 rows=100 loops=1) Total runtime: 3892.253 ms (3 rows) I wouldn't have expected that Index Scan to be so slow. In comparison, I added another column to the table, and cached the results there, and the index scan on the new column is way faster: words=# alter table word add column difficulty integer; ALTER TABLE words=# update word set difficulty=word_difficulty(word); UPDATE 41946 words=# create index word_difficulty_idx on word(difficulty); CREATE INDEX words=# explain analyse select * from word order by difficulty limit 100; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..89.89 rows=100 width=48) (actual time=0.028..0.646 rows=100 loops=1) -> Index Scan using word_difficulty_idx on word (cost=0.00..37706.32 rows=41946 width=48) (actual time=0.023..0.341 rows=100 loops=1) Total runtime: 0.870 ms (3 rows) So I'll probably just end up using the latter approach, but I'm curious, so I ask if anyone can explain why the indexed function is so slow. Thanks! Mikelin
