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

On Fri, Mar 1, 2013 at 1:21 AM, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:

> On 01/03/2013 00:19, Chris Hanks wrote:
> > On Thu, Feb 28, 2013 at 6:00 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> > <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
> >
> > Chris Hanks <christopher(dot)m(dot)hanks(at)gmail(dot)com
> > <mailto: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.
>
> Off the top of my head, I'd imagine it's as simple as:
>
> create view ... as
> select * from my_function(...);
>
> :-)
>
> Ray.
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod(at)iol(dot)ie
>

Sorry, I don't understand. I'm able to make a function that takes an
integer and uses it in the subselect as "WHERE fkey1 = arg", and that works
as I expect it to and it's plenty fast. But I don't see how to write a view
to take advantage of this function - what arguments would go in
my_function(...) when I'm declaring the view?

Chris