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)
-> Bitmap Index 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.00 rows=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;
QUERY PLAN
-----------------------------------------------------------------------
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.00 rows=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 <alvhe...@alvh.no-ip.org>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
