From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Thomas Mayer <thomas.mayer@student.kit.edu>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: window function induces full table scan
Date: Thu, 02 Jan 2014 17:43:12 -0500
Message-ID: <18380.1388702592@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMkU=1zcZH+7_kzAS_jWR-soz7YWUeu6nYeTtAMhiDt6BQKTpw@mail.gmail.com>
References: <52C5DAD1.20109@student.kit.edu>
<17191.1388699570@sss.pgh.pa.us>
<CAMkU=1zcZH+7_kzAS_jWR-soz7YWUeu6nYeTtAMhiDt6BQKTpw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Jeff Janes <jeff.janes@gmail.com> writes:
> On Thu, Jan 2, 2014 at 1:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It's possible that in the specific case you exhibit here, pushing down
>> the clause wouldn't result in changes in the window function's output for
>> the selected rows, but the optimizer doesn't have enough knowledge about
>> window functions to determine that.
> A restriction in the WHERE clause which corresponds to the PARTITION BY
> should be pushable, no? I think it doesn't need to understand the internal
> semantics of the window function itself, just of the PARTITION BY, which
> should be doable, at least in principle.
If the restriction clause must give the same answer for any two rows of
the same partition, then yeah, we could in principle push it down without
knowing anything about the specific window function. It'd be a less than
trivial test to make, I think. In any case, it's not a "bug" that the
optimizer doesn't do this currently.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: tgl@sss.pgh.pa.us, jeff.janes@gmail.com, thomas.mayer@student.kit.edu
Subject: Re: window function induces full table scan
In-Reply-To: <18380.1388702592@sss.pgh.pa.us>
* 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