Thread: Window functions seem to inhibit push-down of quals into views

Hi,

I've got a table and view defined like this:

CREATE TABLE foo AS SELECT a, a % 10 AS b FROM generate_series(1, 100000) a;
CREATE INDEX a_b ON foo (b);
CREATE VIEW bar AS SELECT a, b, lead(a, 1) OVER () FROM foo;

Now, if I query the table directly instead of going through the view, a
WHERE condition can be pushed down to the table scan:

explain select a, b, lead(a, 1) over () from foo where b = 2;                               QUERY PLAN
              
 
---------------------------------------------------------------------------WindowAgg  (cost=12.14..488.72 rows=500
width=8) ->  Bitmap Heap Scan on foo  (cost=12.14..482.47 rows=500 width=8)        Recheck Cond: (b = 2)        ->
BitmapIndex Scan on a_b  (cost=0.00..12.01 rows=500 width=0)              Index Cond: (b = 2)
 
(5 filas)

However, if I instead query the view, the qual is applied to a SubqueryScan
instead, and the table is scanned with no qual at all:

alvherre=# explain select * from bar where b = 2;                             QUERY PLAN

-----------------------------------------------------------------------Subquery Scan bar  (cost=0.00..3943.00 rows=500
width=12) Filter: (bar.b = 2)  ->  WindowAgg  (cost=0.00..2693.00 rows=100000 width=8)        ->  Seq Scan on foo
(cost=0.00..1443.00rows=100000 width=8)
 
(4 filas)

The view is behaving like this:

alvherre=# explain select * from (select a, b, lead(a, 1) over () from foo) b where b = 2;
QUERYPLAN                               
 
-----------------------------------------------------------------------Subquery Scan b  (cost=0.00..3943.00 rows=500
width=12) Filter: (b.b = 2)  ->  WindowAgg  (cost=0.00..2693.00 rows=100000 width=8)        ->  Seq Scan on foo
(cost=0.00..1443.00rows=100000 width=8)
 
(4 filas)



This is a killer for useful views on top of queries with window
functions :-(

Is this a optimizer shortcoming?

-- 
Álvaro Herrera <alvherre@alvh.no-ip.org>

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> CREATE TABLE foo AS SELECT a, a % 10 AS b FROM generate_series(1, 100000) a;
> CREATE INDEX a_b ON foo (b);
> CREATE VIEW bar AS SELECT a, b, lead(a, 1) OVER () FROM foo;

> explain select a, b, lead(a, 1) over () from foo where b = 2;
> explain select * from bar where b = 2;

Those are not equivalent queries.  In the first case b=2 is supposed to be
applied before window function evaluation, in the second case not.
        regards, tom lane