Search Postgresql Archives

Re: Poor performance when using a window function in a view

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Feb 27, 2013 at 10:18 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
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

What would that look like? I've googled around for an example of what you're talking about, but I'm not finding anything. I think I know how to write a SQL function that will return a set of rows given a fkey1 value, but I don't see how I'd turn that into a view...?

Thanks!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux