Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 5F3E963346C for ; Fri, 5 Feb 2010 21:43:04 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 15750-01 for ; Sat, 6 Feb 2010 01:42:37 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-px0-f194.google.com (mail-px0-f194.google.com [209.85.216.194]) by mail.postgresql.org (Postfix) with ESMTP id 72679632DFE for ; Fri, 5 Feb 2010 21:42:53 -0400 (AST) Received: by pxi32 with SMTP id 32so1046109pxi.15 for ; Fri, 05 Feb 2010 17:42:51 -0800 (PST) MIME-Version: 1.0 Received: by 10.142.201.14 with SMTP id y14mr944100wff.95.1265420139490; Fri, 05 Feb 2010 17:35:39 -0800 (PST) In-Reply-To: <4B6BFE33.5080809@gmail.com> References: <603c8f071002031905w51dbea37y148284ea6546feda@mail.gmail.com> <2f4958ff1002040028j29e907c6l47e1f5efe83b667d@mail.gmail.com> <4B6BFE33.5080809@gmail.com> Date: Fri, 5 Feb 2010 20:35:39 -0500 Message-ID: Subject: Re: Slow query: table iteration (8.3) From: Glenn Maynard To: pgsql-performance@postgresql.org Content-Type: text/plain; charset=UTF-8 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201002/104 X-Sequence-Number: 37525 On Fri, Feb 5, 2010 at 6:17 AM, Yeb Havinga 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