From: Robert Haas <robertmhaas@gmail.com>
To: Glenn Maynard <glenn@zewt.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query: table iteration (8.3)
Date: Sat, 6 Feb 2010 01:31:01 -0500
Message-ID: <603c8f071002052231r4f456b6fx76f9497c4f8b8fd9@mail.gmail.com> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: robertmhaas@gmail.com, glenn@zewt.org
Subject: Re: Slow query: table iteration (8.3)
In-Reply-To: <603c8f071002052231r4f456b6fx76f9497c4f8b8fd9@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox