Hi. Sorry if this is repetitive, I tried posting to pgsql-performance first but I think it got stuck in moderation.
---
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
---
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
