From: Glenn Maynard <glenn@zewt.org>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query: table iteration (8.3)
Date: Fri, 5 Feb 2010 20:35:39 -0500
Message-ID: <bd36f99e1002051735i32a2c86blb295ea9edea89547@mail.gmail.com> (raw)
In-Reply-To: <4B6BFE33.5080809@gmail.com>
References: <bd36f99e1001291949r2e769e8i7050a832b8d5a3ef@mail.gmail.com>
<603c8f071002031905w51dbea37y148284ea6546feda@mail.gmail.com>
<bd36f99e1002040024i24a2c78vfad45611946c2ae9@mail.gmail.com>
<2f4958ff1002040028j29e907c6l47e1f5efe83b667d@mail.gmail.com>
<bd36f99e1002040109w67aa1d71hc91261d183fad111@mail.gmail.com>
<bd36f99e1002041904w37cac39jeb9d60156a06c151@mail.gmail.com>
<4B6BFE33.5080809@gmail.com>
On Fri, Feb 5, 2010 at 6:17 AM, Yeb Havinga <yebhavinga@gmail.com> wrote:
> and the cache is used between each row of test_users. The plan is with a
> parameter, that means the optimizer could not make use of an actual value
> during planning. However, your test case is clever in the sense that there
> is an index on users and score and the sql function has an order by that
> matches the index, so the planner can avoid a sort by accessing the test
> table using the index.
That's why the index exists. The point is that the window function
doesn't use the index in this way, and (I think) does a complete index
scan.
It's not just about avoiding a sort, but avoiding touching all of the
irrelevant data in the index and just index searching for each
user_id. The window function appears to scan the entire index. In
principle, it could skip all of the "rank() > 1" data with an index
search, which I'd expect to help many uses of rank(); I assume that's
just hard to implement.
I'll probably be implementing the "temporary functions" approach
tonight, to help Postgres optimize the function. Maybe some day,
Postgres will be able to inline functions in this case and that won't
be needed...
--
Glenn Maynard
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: glenn@zewt.org
Subject: Re: Slow query: table iteration (8.3)
In-Reply-To: <bd36f99e1002051735i32a2c86blb295ea9edea89547@mail.gmail.com>
* 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