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

Hi Chris,

You don't need to make a a full view - to join it later to "less rows
number table")

If you have, function what takes fkey1 as input parameter and returns SET
OF (type of your values_view)

i.e.

CREATE OR REPLACE FUNCTION get_filtered_values_view(in_fkey1 integer)
RETURNS SETOF values_view AS
$BODY$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
having fkey1 = $1
) t$BODY$
LANGUAGE sql STABLE
COST 100
ROWS 1000;
ALTER FUNCTION get_filtered_values_view(integer)
OWNER TO postgres;

Then you can make new function what takes values from table you would like
join to view:

CREATE OR REPLACE FUNCTION get_filtered_values_view_joined()
RETURNS SETOF values_view AS
$BODY$
SELECT get_filtered_values_view(
fkey1
)
FROM
(SELECT DISTINCT fkey1 FROM smaller_table_for_join) t
$BODY$
LANGUAGE sql STABLE
COST 100
ROWS 1000;
ALTER FUNCTION get_filtered_values_joined()
OWNER TO postgres;

then you can encapsulate it to final view:
CREATE VIEW final_derived_view AS
SELECT * FROM get_filtered_values_joined()

2013/3/1 Chris Hanks <christopher(dot)m(dot)hanks(at)gmail(dot)com>

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