view_with_window_aggregation

view_with_window_agg.sql

postgres=# table t;
 id | value
----+-------
(0 rows)

postgres=# insert into t select x, x from generate_series(1, 100) x order by random();
INSERT 0 100
postgres=# create view myview as select id, value, row_number() over (order by id) from t;
CREATE VIEW
postgres=# analyze t;
ANALYZE
postgres=# explain analyze select id, value, row_number() over (order by id) from t where id < 10;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=2.37..2.51 rows=8 width=16) (actual time=0.035..0.040 rows=9 loops=1)
   ->  Sort  (cost=2.37..2.39 rows=8 width=8) (actual time=0.028..0.029 rows=9 loops=1)
         Sort Key: id
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on t  (cost=0.00..2.25 rows=8 width=8) (actual time=0.011..0.017 rows=9 loops=1)
               Filter: (id < 10)  ----- filter before agg
               Rows Removed by Filter: 91
 Planning Time: 0.107 ms
 Execution Time: 0.059 ms
(9 rows)

postgres=# explain analyze select * from myview where id < 10;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Subquery Scan on myview  (cost=5.32..8.32 rows=8 width=16) (actual time=0.041..0.088 rows=9 loops=1)
   Filter: (myview.id < 10)  --filter after agg
   Rows Removed by Filter: 91
   ->  WindowAgg  (cost=5.32..7.07 rows=100 width=16) (actual time=0.040..0.081 rows=100 loops=1)
         ->  Sort  (cost=5.32..5.57 rows=100 width=8) (actual time=0.032..0.038 rows=100 loops=1)
               Sort Key: t.id
               Sort Method: quicksort  Memory: 29kB
               ->  Seq Scan on t  (cost=0.00..2.00 rows=100 width=8) (actual time=0.005..0.012 rows=100 loops=1)
 Planning Time: 0.086 ms
 Execution Time: 0.126 ms
(10 rows)

postgres=# create view mysimpleview as select id, value from t;
CREATE VIEW
postgres=# explain analyze select * from mysimpleview where id < 10;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..2.25 rows=8 width=8) (actual time=0.008..0.014 rows=9 loops=1)
   Filter: (id < 10)
   Rows Removed by Filter: 91
 Planning Time: 0.053 ms
 Execution Time: 0.032 ms
(5 rows)

postgres=# explain analyze select * from t where id < 10;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..2.25 rows=8 width=8) (actual time=0.011..0.017 rows=9 loops=1)
   Filter: (id < 10)
   Rows Removed by Filter: 91
 Planning Time: 0.051 ms
 Execution Time: 0.028 ms
(5 rows)