On Fri, Feb 5, 2010 at 8:35 PM, Glenn Maynard <glenn@xxxxxxxx> wrote: > On Fri, Feb 5, 2010 at 6:17 AM, Yeb Havinga <yebhavinga@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
