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

On Fri, Mar 1, 2013 at 3:59 AM, Chris Hanks
<christopher.m.hanks@gmail.com> wrote:
> 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?

First let's clearly state the problem - create some test data:
postgres=# create table foo as select v1, v2 from (select
generate_series(1,10) v1) q1, (select generate_series(1,10000) v2) q2;
postgres=# create table bar as select v1 from generate_series(1,10) v1;

foo is data table, bar defines the key around which we have the data.

create an index:
postgres=# create index on foo(v1);

simple lookup is optimized obviously:
postgres=# explain select * from foo where v1 = 7;
                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using foo_v1_idx on foo  (cost=0.00..4.38 rows=1 width=8)
   Index Cond: (v1 = 50)

get indexed lookup for simple window function:
postgres=# explain select v1, sum(v2) over(partition by v1 order by
v2) from foo where v1 = 7;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 WindowAgg  (cost=4.39..4.41 rows=1 width=8)
   ->  Sort  (cost=4.39..4.39 rows=1 width=8)
         Sort Key: v2
         ->  Index Scan using foo_v1_idx on foo  (cost=0.00..4.38
rows=1 width=8)
               Index Cond: (v1 = 50)

push to subquery (essentially what view does) and we lose the optimization:
postgres=# explain select * from (select v1, sum(v2) over(partition by
v1 order by v2) from foo) q  where v1 = 7;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Subquery Scan on q  (cost=9747.82..12997.82 rows=1 width=12)
   Filter: (q.v1 = 50)
   ->  WindowAgg  (cost=9747.82..11747.82 rows=100000 width=8)
         ->  Sort  (cost=9747.82..9997.82 rows=100000 width=8)
               Sort Key: foo.v1, foo.v2
               ->  Seq Scan on foo  (cost=0.00..1443.00 rows=100000 width=8)

let's work around it!
postgres=# CREATE OR REPLACE FUNCTION getfoovals(
  v1 INOUT int,
  sumv2 OUT BIGINT) RETURNS SETOF RECORD AS
$$
  select
    v1,
    sum(v2)
    over(partition by v1 order by v2)
  from foo where foo.v1 = getfoovals.v1;
$$ LANGUAGE SQL STABLE;

select * from getfoovals(7);
 v1 |  sumv2
----+----------
  7 |        1
  7 |        3
  7 |        6
  7 |       10
  7 |       15
  7 |       21
<snip>

abstract to view:
postgres=# CREATE OR REPLACE VIEW foovals AS
  select
    v1,
    (getfoovals(v1)).sumv2
  from bar;

postgres=#  select * from foovals where v1 = 7;

Main problem with this technique is awkwardness around using column
lest set returning function if it returns > 1 column...if you'r not
careful you can get extra invocations of function.   you work hack
around this to some degree via:
*) offset 0 hacks
*) return type coersion hacks
*) 9.3 LATERAL feature completely nails it

Can't use WITH because it fences of the optimization.

This (window function optimization fencing) is probably #1 performance
gotcha I hit in everyday coding after LATERAL and lack of better
ability to inline simple SQL functions.

merlin