On Wed, Feb 27, 2013 at 8:22 PM, Chris Hanks <christopher.m.hanks@xxxxxxxxx> wrote: > Hi. Sorry if this is repetitive, I tried posting to pgsql-performance first > but I think it got stuck in moderation. > > I'm trying to create a view that uses a window function, but it seems that > Postgres is 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) > ); > > -- This is kind of hacky, but it roughly resembles the size and distribution > of 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 if postgres isn't capable > of optimizing this right now, is there a workaround of some kind? This is a > view I'd like to be able to join a smaller table against. this comes up a lot. only way to expose as a view is to push the query into a set returning function which you then wrap into a view. downside is that any query except on fkey1/fkey 2 will have to fully materialize view. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
