From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thomas Mayer <thomas.mayer@student.kit.edu>
cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: window function induces full table scan
In-reply-to: <52C6F712.6040804@student.kit.edu>
References: <52C5DAD1.20109@student.kit.edu> <17191.1388699570@sss.pgh.pa.us>
 <CAMkU=1zcZH+7_kzAS_jWR-soz7YWUeu6nYeTtAMhiDt6BQKTpw@mail.gmail.com>
 <18380.1388702592@sss.pgh.pa.us> <20167.1388706955@sss.pgh.pa.us>
 <52C63E8B.2040806@student.kit.edu> <9269.1388760865@sss.pgh.pa.us>
 <52C6F712.6040804@student.kit.edu>
Comments: In-reply-to Thomas Mayer <thomas.mayer@student.kit.edu>
 message dated "Fri, 03 Jan 2014 18:44:50 +0100"
Date: Fri, 03 Jan 2014 13:04:13 -0500
Message-ID: <4827.1388772253@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Thomas Mayer <thomas.mayer@student.kit.edu> writes:
> ... "mark" ...: Do I understand you correctly, that you prefer doing the 
> decision elsewhere and store the result (safe/unsafe) boolean value 
> besides to the subquery output fields? For the push-down, a subquery 
> output field must be available anyways.

See check_output_columns().  The infrastructure for deciding whether
a potentially-pushable qual refers to any unsafe subquery outputs already
exists; we just need to extend it to consider outputs unsafe if they
don't appear in all PARTITION BY lists.

>> Offhand I think the details of testing whether a given output column
>> appears in a given partition clause are identical to testing whether
>> it appears in the distinctClause.  So you'd just be mechanizing running
>> through the windowClause list to verify whether this holds for all
>> the WINDOW clauses.

> When a field is element of all PARTITION BY clauses of all window 
> functions, it does not mean that this field is distinct.

No, I didn't say that.  What I meant was that (a) the is_pushdown_safe
logic can treat non-partitioning subquery outputs much like non-DISTINCT
outputs, and (b) the parsetree representation of PARTITION BY is enough
like DISTINCT ON that the same kind of test (viz, a targetIsInSortList
call) will serve.

I think you need to read the code around subquery_is_pushdown_safe and
qual_is_pushdown_safe some more.

			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