Index on function less well cached than "regular" index ?

From: "Paul Mackay" <mackaypaul(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Index on function less well cached than "regular" index ?
Date: 2006-04-24 10:53:29
Message-ID: 786c2f6d0604240353n7853ef1fn148611ed2674044c@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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 ?

Thanks,
Paul

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Bruno Almeida do Lago 2006-04-24 14:36:18 Re: GROUP BY Vs. Sub SELECT
Previous Message Markus Schaber 2006-04-24 08:29:44 Re: Recovery will take 10 hours