Thread: Push predicate down in view containing window function
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)
Philippe Girolami <philippe.girolami@sensorly.com> writes:
> 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
thecolumns. 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
predicatedown ?
Because there is zero optimization around window functions right now.
It certainly isn't smart enough to decide that it'd be safe to push
down a constraint that only mentions partitioning columns.
regards, tom lane
Ok so is there a way i can do something similar ? Would a function returning rows and taking the extra predicate
'values'as parameters be as optimized as the 'good' query in my first email ?
Thanks.
Tom Lane <tgl@sss.pgh.pa.us> a écrit :
Philippe Girolami <philippe.girolami@sensorly.com> writes:
> 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
thecolumns. 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
predicatedown ?
Because there is zero optimization around window functions right now.
It certainly isn't smart enough to decide that it'd be safe to push
down a constraint that only mentions partitioning columns.
regards, tom lane
On Fri, Nov 15, 2013 at 12:43 AM, Philippe Girolami <philippe.girolami@sensorly.com> wrote: > Ok so is there a way i can do something similar ? Would a function returning rows and taking the extra predicate 'values'as parameters be as optimized as the 'good' query in my first email ? There is only one way I know of to do it (force a qual into a view wrapped into a subquery). It's tweaky, particularly with pre-9.3 LATERAL. The basic MO is to put the window function into SQL function in order to be able to force the qual into the inner query with a function parameter. Then, you make a view that cross products the possible arguments to the function and LATERALS them into the set returning function (it's possible, but difficult, to do it without lateral). As long as the 'arguments' expressed in the view are always specified in the query that hits the view performance should be good. If this sounds like something you'd like to tackle, maybe I can work up an example. merlin