Materialized view aggregate index example

gistfile1.txt

 col1 | col2 | col_1_sum 
------+------+-----------
    1 |    2 |         2
    1 |    2 |         2
(2 rows)

gistfile2.sql

DROP DATABASE IF EXISTS aggregate_index_test;
CREATE DATABASE aggregate_index_test;

\c aggregate_index_test;

DROP table IF EXISTS test_table;
CREATE TABLE test_table (
  col1 INTEGER,
  col2 INTEGER
);

insert into test_table values(1, 2);
insert into test_table values(1, 2);

CREATE MATERIALIZED VIEW test_aggregate_sum
AS SELECT *, (SELECT SUM(col1) FROM test_table) AS col_1_sum
FROM test_table;

CREATE INDEX col_1_sum_index ON test_aggregate_sum(col_1_sum);

select * from test_aggregate_sum;