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)
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 |