pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex on function less well cached than "regular" index ?
2+ messages / 2 participants
[nested] [flat]
* Index on function less well cached than "regular" index ?
@ 2006-04-24 10:53 Paul Mackay <mackaypaul@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Paul Mackay @ 2006-04-24 10:53 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Index on function less well cached than "regular" index ?
@ 2006-04-24 22:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Paul Mackay <mackaypaul@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2006-04-24 22:11 UTC (permalink / raw)
To: Paul Mackay <mackaypaul@gmail.com>; +Cc: pgsql-performance
"Paul Mackay" <mackaypaul@gmail.com> writes:
> ...
> 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.
> ...
> Could the problem be that an index on a function result is not cached or
> less well cached ?
I can't see how that would be. The index machinery has no idea what
it's indexing, and the kernel disk cache even less.
Perhaps the majority of the runtime is going somewhere else, like the
initial evaluation of the function value to compare against? Or maybe
you've found some inefficiency in the planner's handling of function
indexes. Try comparing EXPLAIN ANALYZE output for the two cases to see
if the discrepancy exists during query runtime, or if it's upstream at
plan time.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-04-24 22:11 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-24 10:53 Index on function less well cached than "regular" index ? Paul Mackay <mackaypaul@gmail.com>
2006-04-24 22:11 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox