From: Tom Lane <tgl@sss.pgh.pa.us>
To: Paul Mackay <mackaypaul@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index on function less well cached than "regular" index ?
Date: Mon, 24 Apr 2006 18:11:44 -0400
Message-ID: <7022.1145916704@sss.pgh.pa.us> (raw)
In-Reply-To: <786c2f6d0604240353n7853ef1fn148611ed2674044c@mail.gmail.com>
References: <786c2f6d0604240353n7853ef1fn148611ed2674044c@mail.gmail.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mackaypaul@gmail.com
Subject: Re: Index on function less well cached than "regular" index ?
In-Reply-To: <7022.1145916704@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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