Re: Slow query: table iteration (8.3)

On Fri, Feb 5, 2010 at 6:17 AM, Yeb Havinga <yebhavinga(at)gmail(dot)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