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

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

On 01/03/2013 00:19, Chris Hanks wrote:
> On Thu, Feb 28, 2013 at 6:00 AM, Tom Lane <tgl@sss.pgh.pa.us

> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
>     Chris Hanks <christopher.m.hanks@gmail.com

>     <mailto:christopher.m.hanks@gmail.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@iol.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