Re: Poor performance when using a window function in a view

Chris Hanks <christopher(dot)m(dot)hanks(at)gmail(dot)com> writes:
> create or replace view values_view as
> select fkey1, fkey3,
> (derived1 / max(derived1) over (partition by fkey1)) as derived1,
> (derived2 / sum(derived1) over (partition by fkey1)) as derived2
> from (
> select fkey1, fkey3,
> cast(sum((case when (value > 0.0) then 4 else 1 end)) as double
> precision) as derived1,
> sum((case when (value > 0.0) then (value * 4) else (value + 1) end)) as
> derived2
> from values
> group by fkey1, fkey3
> ) as t1;

> -- This query requires a sequential scan on values, though all the data it
> needs could be found much more efficiently with an index scan.
> explain analyze select * from values_view where fkey1 = 1263;

To use the outer WHERE clause as an index constraint, postgres would
have to prove that scanning only the rows with fkey1 = 1263 would still
find all the rows that would get examined by the window functions ---
and in this case, it's not only the window functions that make that less
than obvious, but the grouped aggregates in the sub-select below them.
There's not nearly that amount of intelligence in the system about
window functions, as yet. So you'll have to write out the query
longhand and put the WHERE clause at the lower level, if you want this
optimization to happen.

regards, tom lane