Re: No optimization with a partition window in a view

Hello,

At Mon, 17 Feb 2014 23:10:53 -0500, Tom Lane wrote
> Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp> writes:
> > Hello, this seems to be a matter of subquery pushdown, query
> > transform involving subqueries or how to deal with views in
> > planner, rather than a bug.
>
> In general, pushing a WHERE clause down through a window function call
> *would* be a bug.

I presumed without thought the first two queries are equivalent, but

http://www.postgresql.org/docs/9.2/static/tutorial-window.html

| The rows considered by a window function are those of the
| "virtual table" produced by the query's FROM clause as filtered
| by its WHERE, GROUP BY, and HAVING clauses if any. For example,
| a row removed because it does not meet the WHERE condition is
| not seen by any window function.

Pushing down WHERE clause relating to window functions is
certainly a bug for window functions in most cases.

> I think it should be safe in this case because the WHERE clause
> matches the window functions' partition clauses, so that
> applying the WHERE removes either all or none of the rows of
> any particular partition.

I understood that after examining data. How stupid!

> We've not gotten around to that type of refinement in window
> function planning, yet.  It would take some infrastructure that
> doesn't exist now --- I don't recall that we have any code that
> tries to make that particular kind of proof.

# Althgough I dont't see the landscape of what kind of these
# optimizations could there be,

Subquery comes to me to seem to be a firm obstacle for many
things :-( But it makes everything simpler, too.

The infrastructure is, in simple imagination(?), that for
examining condition, pushing down, repeated exec time estimation
and selection involving upper nodes of the subquery, but this is
too naive design as it takes too long time to make exec plan as
you mentioned before , besides of ugliness. (But multiple
plannings for one subquery seems not needed for this case.)

Larger scale transformations which can dissolve subqueries would
be better. Feeling overwhelmed..

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center