I have a table of ~ 41 000 rows with an index on the result of a function applied to a certain text column (the function basically removes "neutral" or common words like "the","on","a", etc. from the string).
I then execute a query with a where clause on this function result with an order by on the function result also and a limit of 200. Logically the EXPLAIN shows that an index scan is used by the planner. A query returning the maximum 200 number of records takes around 20 ms. What is surprising is that the same query executed several times takes practically the same time, as if the result was not cached.
To test this, I then added a new text column to the same table, populating it with the function result mentioned above. Now, I create an index on this new column and execute the same query as described above (order by on the new column and limit 200). The execution plan is exactly the same, except that the new index is used of course. The first execution time is similar, i.e. 20 ms approx., but the next executions of the same query take about 2 ms (i.e to say a 10 to 1 difference). So this time, it seems that the result is properly cached.
Could the problem be that an index on a function result is not cached or less well cached ?