Push predicate down in view containing window function

From: Philippe Girolami <philippe(dot)girolami(at)sensorly(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Push predicate down in view containing window function
Date: 2013-11-14 21:50:23
Message-ID: CEAB042D.39056%philippe.girolami@sensorly.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dang Minh Huong 2013-11-14 22:14:18 Re: Equivalent syntax of PL/SQL using array in PL/pgSQL
Previous Message Tomas Vondra 2013-11-14 21:05:30 Re: Tuning 9.3 for 32 GB RAM