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

On Thu, Feb 28, 2013 at 6:00 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

Ok, that makes sense, thanks.

Can anyone point me to an example of wrapping a function in a view, like
Merlin suggested? I'm not sure how that would work.