[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: [PERFORM] Using a window function in a view
From: Chris Hanks <christopher.m.hanks () gmail ! com>
Date: 2013-02-21 23:37:10
Message-ID: CAK7KUdBm_r9ikvEm4TFioXfQYuQVVFNAMiVV+O0Z3etBQ_H5dA () mail ! gmail ! com
[Download RAW message or body]
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
[Attachment #3 (text/html)]
<div dir="ltr"><div style><span \
style="color:rgb(51,51,51);font-family:monospace;font-size:12px;white-space:pre-wrap;background-color:rgb(248,248,248)">I'm \
trying to create a view that uses a window function, but it seems that Postgres is \
apparently unable to optimize it. </span><span \
style="color:rgb(51,51,51);font-family:monospace;font-size:12px;white-space:pre-wrap;background-color:rgb(248,248,248)">Here's \
a reproduction of my situation </span><span \
style="background-color:rgb(248,248,248);color:rgb(51,51,51);font-family:monospace;font-size:12px;white-space:pre-wrap">with \
9.2.2:</span></div>
<span style="color:rgb(51,51,51);font-family:monospace;font-size:12px;white-space:pre-wrap;background-color:rgb(248,248,248)"><div><span \
style="color:rgb(51,51,51);font-family:monospace;font-size:12px;white-space:pre-wrap;background-color:rgb(248,248,248)"><br>
</span></div><div><span \
style="color:rgb(51,51,51);font-family:monospace;font-size:12px;white-space:pre-wrap;background-color:rgb(248,248,248)">---</span></div><div><span \
style="color:rgb(51,51,51);font-family:monospace;font-size:12px;white-space:pre-wrap;background-color:rgb(248,248,248)"><br>
</span></div>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;
<br></span><div><span \
style="color:rgb(51,51,51);font-family:monospace;font-size:12px;white-space:pre-wrap;background-color:rgb(248,248,248)">-- \
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;</span><br></div><div><span \
style="color:rgb(51,51,51);font-family:monospace;font-size:12px;white-space:pre-wrap;background-color:rgb(248,248,248)"><br></span></div>
<div><span style="color:rgb(51,51,51);font-family:monospace;font-size:12px;white-space:pre-wrap;background-color:rgb(248,248,248)">---</span></div><div><span \
style="color:rgb(51,51,51);font-family:monospace;font-size:12px;white-space:pre-wrap;background-color:rgb(248,248,248)"><br>
</span></div><div><span \
style="color:rgb(51,51,51);font-family:monospace;font-size:12px;white-space:pre-wrap;background-color:rgb(248,248,248)">Can \
anyone suggest a way to rewrite this query, or maybe a workaround of some \
kind?</span><span style="color:rgb(51,51,51);font-family:monospace;font-size:12px;white-space:pre-wrap;background-color:rgb(248,248,248)"><br>
</span></div><div><span \
style="color:rgb(51,51,51);font-family:monospace;font-size:12px;white-space:pre-wrap;background-color:rgb(248,248,248)"><br></span></div><div \
style><span style="color:rgb(51,51,51);font-family:monospace;font-size:12px;white-space:pre-wrap;background-color:rgb(248,248,248)">Thanks, \
Chris</span></div>
</div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic