From: Thomas Mayer <thomas.mayer@student.kit.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: window function induces full table scan
Date: Thu, 2 Jan 2014 23:45:56 +0100
Message-ID: <52C5EC24.9060206@student.kit.edu> (raw)
In-Reply-To: <17191.1388699570@sss.pgh.pa.us>
References: <52C5DAD1.20109@student.kit.edu>
<17191.1388699570@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
You understood me correctly, Tom.
As you mention, the result would be correct in my case:
- The window function is performing a "PARTITION BY user_id".
- user_id is used for the WHERE condition.
I agree, that in general (PARTITION BY and WHERE don't use the same set
of attributes), incorrect results could occur when performing the WHERE
condition before performing the window function.
However, in this special case, PARTITION BY and WHERE use the same set
of attributes which safely allows some optimization.
In fact, this is getting complicated when multiple window functions with
different PARTITION BY's are used in one statement.
I think, performing the WHERE condition before performing the window
function would be safe if the WHERE condition attribute is element of
the PARTITION-BY-set of attributes of _every_ window function of the
statement.
To ensure correctness, WHERE condition attributes which are _not_
element of the PARTITION-BY-set of attributes of _every_ window function
of the statement need to be performed after performing the window function.
So, the optimizer could check if it's safe or not.
Regards,
Thomas
Am 02.01.2014 22:52, schrieb Tom Lane:
> Thomas Mayer <thomas.mayer@student.kit.edu> writes:
>> When querying a view with a WHERE condition, postgresql normally is able
>> to perform an index scan which reduces time for evaluation dramatically.
>
>> However, if a window function is evaluated in the view, postgresql is
>> evaluating the window function before the WHERE condition is applied.
>> This induces a full table scan.
>
> You haven't exactly provided full details, but it looks like you are
> thinking that WHERE clauses applied above a window function should
> be pushed to below it. A moment's thought about the semantics should
> convince you that such an optimization would be incorrect: the window
> function would see fewer input rows than it should, and therefore would
> (in general) return the wrong values for the selected rows.
>
> 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.
>
> regards, tom lane
> .
>
--
======================================
Thomas Mayer
Durlacher Allee 61
D-76131 Karlsruhe
Telefon: +49-721-2081661
Fax: +49-721-72380001
Mobil: +49-174-2152332
E-Mail: thomas.mayer@student.kit.edu
=======================================
--
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: thomas.mayer@student.kit.edu, tgl@sss.pgh.pa.us
Subject: Re: window function induces full table scan
In-Reply-To: <52C5EC24.9060206@student.kit.edu>
* 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