Poor performance when using a window function in a view - Mailing list pgsql-general

From Chris Hanks
Subject Poor performance when using a window function in a view
Date
Msg-id CAK7KUdDpfZ+C1vWiTPcJ7V2qP1NZaM9ZYF=fDPwXU_6j5k=Jzg@mail.gmail.com
Whole thread Raw
Responses Re: Poor performance when using a window function in a view
Re: Poor performance when using a window function in a view
List pgsql-general
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.

Thanks - Chris

pgsql-general by date:

Previous
From: François Beausoleil
Date:
Subject: Re: What could cause a temp table to disappear?
Next
From: Merlin Moncure
Date:
Subject: Re: Poor performance when using a window function in a view