DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=dmih8Zs90GVNxEUeqIvuktDxY0+YmcLxRmVUtxtZa0QPt+yLUMsSNhtYrPCseU/7Hn
	FxW6Tfr9AlB/22N45SxEPNhjim7noF/+DfR8uB9gQ0A8YzqB3TKJNd60DpU0vA5BZpPS
	x7CdlOHWrmZMYcmu2K5pyRtIyqfeNxcon+KPo=
MIME-Version: 1.0
In-Reply-To: <bd36f99e1002051735i32a2c86blb295ea9edea89547@mail.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>
	<bd36f99e1002051735i32a2c86blb295ea9edea89547@mail.gmail.com>
Date: Sat, 6 Feb 2010 01:31:01 -0500
Message-ID: <603c8f071002052231r4f456b6fx76f9497c4f8b8fd9@mail.gmail.com>
Subject: Re: Slow query: table iteration (8.3)
From: Robert Haas <robertmhaas@gmail.com>
To: Glenn Maynard <glenn@zewt.org>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 valu=
e
>> during planning. However, your test case is clever in the sense that the=
re
>> 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. =A0The 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. =A0The window function appears to scan the entire index. =A0In
> 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