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 <alvherre@alvh.no-ip.org>

Search Discussions

  • Tom Lane at Aug 13, 2010 at 10:14 pm

    Alvaro Herrera writes:
    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;
    explain select a, b, lead(a, 1) over () from foo where b = 2;
    explain select * from bar where b = 2;
    Those are not equivalent queries. In the first case b=2 is supposed to be
    applied before window function evaluation, in the second case not.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 13, '10 at 9:48p
activeAug 13, '10 at 10:14p
posts2
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Alvaro Herrera: 1 post Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase