Skip to content

Instantly share code, notes, and snippets.

@djcp
Last active August 29, 2015 14:17
Show Gist options
  • Save djcp/0099e701515ae410ca07 to your computer and use it in GitHub Desktop.
Save djcp/0099e701515ae410ca07 to your computer and use it in GitHub Desktop.
Materialized view aggregate index example
col1 | col2 | col_1_sum
------+------+-----------
1 | 2 | 2
1 | 2 | 2
(2 rows)
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment