Hello,
Using postgres 9.1.9, I have a view that uses a window function. I then query
that view with a predicate on one of the columns. Unfortunately, the predicate
doesn't get pushed down into the view.
Given that the predicate applies to a column that's being partitionned on, why
wouldn't the optimizer push the predicate down ?
create table test_table (col1 text,col2 text);
insert into test_table values ('a','a2');
insert into test_table values ('b','b2');
create or replace view test_view as
select
col1,col2,lead(col2) over w
from test_table
WINDOW w AS (partition by col1 order by col2)
;
BAD (query through view)
public=# explain select * from test_view where col1='a';
QUERY PLAN
--------------------------------------------------------------------------------
Subquery Scan on test_view (cost=60.52..88.47 rows=4 width=96)
Filter: (test_view.col1 = 'a'::text)
-> WindowAgg (cost=60.52..77.72 rows=860 width=64)
-> Sort (cost=60.52..62.67 rows=860 width=64)
Sort Key: test_table.col1, test_table.col2
-> Seq Scan on test_table (cost=0.00..18.60 rows=860 width=64)
GOOD (direct query)
public=# explain select
col1,col2,lead(col2) over w
from test_table
where col1='a'
WINDOW w AS (partition by col1 order by col2)
;
QUERY PLAN
------------------------------------------------------------------------
WindowAgg (cost=20.79..20.86 rows=4 width=64)
-> Sort (cost=20.79..20.80 rows=4 width=64)
Sort Key: col2
-> Seq Scan on test_table (cost=0.00..20.75 rows=4 width=64)
Filter: (col1 = 'a'::text)
(5 rows)
