Re: Slow query: table iteration (8.3)

On Fri, Feb 5, 2010 at 8:35 PM, Glenn Maynard <glenn@zewt.org> wrote:
> 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.

Yeah.  The window function stuff is all pretty new, and I seem to
recall some discussion around the fact that it's not all as
well-optimized as it could be yet.  Maybe someone will feel the urge
to take a whack at that for 9.1.

...Robert