Re: window function induces full table scan

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
> .
>