From: Thomas Mayer <thomas.mayer@student.kit.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: window function induces full table scan
Date: Fri, 3 Jan 2014 05:37:31 +0100
Message-ID: <52C63E8B.2040806@student.kit.edu> (raw)
In-Reply-To: <20167.1388706955@sss.pgh.pa.us>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
I have just cloned the postgresql git repository and checked out the
REL9_3_2 tagged version to have a look at the
src/backend/optimizer/path/allpaths.c file.
As Tom already mentioned, quals are currently not pushed down when
subqueries with window functions occur:
There is a function subquery_is_pushdown_safe(...) which is asserting
that quals are not pushed down when window functions occur:
"
* 2. If the subquery contains any window functions, we can't push quals
* into it, because that could change the results.
[...]
/* Check point 2 */
if (subquery->hasWindowFuncs)
return false;
"
To implement the optimization, subquery_is_pushdown_safe() needs to
return true if pushing down the quals to a subquery which has window
functions is in fact safe ("quals that only reference subquery
outputs that are listed in the PARTITION clauses of all window functions
in the subquery").
Plus, there is a function qual_is_pushdown_safe(...) which contains an
assertion, which might possibly become obsolete:
"
/*
* It would be unsafe to push down window function calls, but at least for
* the moment we could never see any in a qual anyhow. (The same applies
* to aggregates, which we check for in pull_var_clause below.)
*/
Assert(!contain_window_function(qual));
"
Tom, do you think that these two changes could be sufficient? Do you
have a more general aproach in mind?
Best regards
Thomas
Am 03.01.2014 00:55, schrieb Tom Lane:
> I wrote:
>> 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.
>
> On reflection, really this concern is isomorphic to whether or not it is
> safe to push quals down into a SELECT DISTINCT. In principle, we should
> only do that for quals that cannot distinguish values that are seen as
> equal by the equality operator used for DISTINCT. For instance, the
> float8 equality operator treats IEEE minus zero and plus zero as "equal",
> but it's not hard to contrive a WHERE clause that can tell the difference.
> Pushing such a clause down into a SELECT DISTINCT can change the results;
> but we do it anyway, and have done so since the nineties, and I don't
> recall hearing complaints about this.
>
> If we wanted to be really principled about it, I think we'd have to
> restrict pushdown to quals that tested subquery outputs with operators
> that are members of the relevant equality operator's btree opclass.
> Which would cause a lot of howls of anguish, while making things better
> for a set of users that seems to be about empty.
>
> So maybe it'd be all right to push down quals that only reference subquery
> outputs that are listed in the PARTITION clauses of all window functions
> in the subquery. I think that'd be a reasonably straightforward extension
> of the existing tests in allpaths.c.
>
> 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: thomas.mayer@student.kit.edu, tgl@sss.pgh.pa.us, jeff.janes@gmail.com
Subject: Re: window function induces full table scan
In-Reply-To: <52C63E8B.2040806@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