Thread

Using a window function in a view

Chris Hanks <christopher.m.hanks@gmail.com> — 2013-02-21T23:37:10Z

I'm trying to create a view that uses a window function, but it seems that
Postgres is apparently unable to optimize it. Here's a reproduction of my
situation with 9.2.2:

---

drop table if exists values cascade; create table values ( fkey1 integer
not null, fkey2 integer not null, fkey3 integer not null, value float not
null, constraint values_pkey primary key (fkey1, fkey2, fkey3) ); -- Kind
of hacky, but it roughly resembles my dataset. insert into values select
distinct on (fkey1, fkey2, fkey3) i / 12 + 1 as fkey1, i % 4 + 1 as fkey2,
ceil(random() * 10) as fkey3, random() * 2 - 1 as value from
generate_series(0, 199999) i; 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;

---

Can anyone suggest a way to rewrite this query, or maybe a workaround of
some kind?

Thanks, Chris