Re: No optimization with a partition window in a view

Hello, this seems to be a matter of subquery pushdown, query
transform involving subqueries or how to deal with views in
planner, rather than a bug.

> I tried to avoid implementing window functions inside my ORM by using a view,
> but it seems the optimizer is missing an obvious optimization and thus doing a
> full table scan.
>
> Affected versions : 9.2.4 and 9.3.2 (9.4 not tested yet)

9.4dev is also "affected".

> How to reproduce :
<snip>

OK_A> => select * from test_history where piece = 42;
OK_B> => select *, lag(location, 1) over w, lead(location, 1) over w from test_history
      where piece = 42 window w as (partition by piece order by date);

> => create view test_history_lag_lead as select *, lag(location, 1) over w,
> lead(location, 1) over w from test_history window w as (partition by piece order
> by date);

NG_C> => select * from test_history_lag_lead where piece = 42;

The equivalent for NG_C is not OK_B but this,

select * from
   (select *, lag(location, 1) over w, lead(location,1) over w
    from test_history window w as (partition by piece order
    by date)) test_history_lag_lead
where piece = 42;

You will see this also falls down to SeqScan. Since views are
treated as monolithic substances. The planner simplly replaces a
view with a subquery and the subquery is planned individually -
separately from the upper part so that result comes. No amendment
is seen in my poor sight so far..


If your objective is simplification of queries or concealing
details and you are allowed to do these in different way,
functions could be usable.

CREATE OR REPLACE FUNCTION test_history_lag_lead(integer)
  RETURNS TABLE(i integer, piece integer, date timestamp with time zone,
                location integer, lag integer, lead integer) AS $$
SELECT *, lag(location, 1) OVER w, lead(location, 1) OVER w
 FROM test_history WHERE piece = $1
 WINDOW w AS (PARTITION BY piece ORDER BY date);
$$ LANGUAGE SQL;

select * from test_history_lag_lead(42);
...
Total runtime: 0.594 ms

(Seems slow but seqscan takes 13 sec on my rig..(altough without
 compile optimizations))


regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center