SQL Server Indexed Views and PostgreSQL Materialized Views

SQL Server Usage

The first index created on a view must be a clustered index. Subsequent indexes can be non-clustered indexes. For more information, see Clustered and nonclustered indexes described in the SQL Server documentation.

Before creating an index on a view, the following requirements must be met:

Examples

Set the required SET options, create a view with the WITH SCHEMABINDING option, and create an index on this view.

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
  QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

CREATE VIEW Sales.Ord_view
WITH SCHEMABINDING
AS
  SELECT SUM(Price*Qty*(1.00-Discount)) AS Revenue,
    OrdTime, ID, COUNT_BIG(*) AS COUNT
  FROM Sales.OrderDetail AS ordet, Sales.OrderHeader AS ordhead
  WHERE ordet.SalesOrderID = ordhead.SalesOrderID
  GROUP BY OrdTime, ID;
GO

CREATE UNIQUE CLUSTERED INDEX IDX_V1
  ON Sales.Ord_view (OrdTime, ID);
GO

For more information, see Create Indexed Views in the SQL Server documentation.

PostgreSQL Usage

PostgreSQL doesn’t support indexed views, but does provide similar functionality with materialized views. You can run queries associated with materialized views, and populate the view data with the REFRESH command.

The PostgreSQL implementation of materialized views has three primary limitations:

In some cases, when the tables are big, full REFRESH can cause performance issues. In this case, you can use triggers to sync between one table to the new table. You can use the new table as an indexed view.

Examples

The following example creates a materialized view named sales_summary using the sales table as the source.

CREATE MATERIALIZED VIEW sales_summary AS
SELECT seller_no,sale_date,sum(sale_amt)::numeric(10,2) as sales_amt
FROM sales
WHERE sale_date < CURRENT_DATE
GROUP BY seller_no, sale_date
ORDER BY seller_no, sale_date;

The following example runs a manual refresh of the materialized view:

REFRESH MATERIALIZED VIEW sales_summary;

Creating a Materialized View

When you create a materialized view in PostgreSQL, it uses a regular database table underneath. You can create database indexes on the materialized view directly and improve performance of queries that access the materialized view.

Example

The following example creates an index on the sellerno and sale_date columns of the sales_summary materialized view.

CREATE UNIQUE INDEX sales_summary_seller
ON sales_summary (seller_no, sale_date);

Summary

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL,
  ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

CREATE VIEW Sales.Ord_view WITH SCHEMABINDING
  AS SELECT SUM(Price*Qty*(1.00-Discount)) AS Revenue,
  OrdTime, ID, COUNT_BIG(*) AS
    COUNT FROM Sales.OrderDetail AS ordet,
  Sales.OrderHeader AS ordhead
    WHERE ordet.SalesOrderID = ordhead.SalesOrderID
    GROUP BY OrdTime, ID;
GO

CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON Sales.Ord_view (OrdTime, ID);
GO

CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM employees;

CREATE OR REPLACE FUNCTION
refresh_mv1()
returns trigger language plpgsql as
$$ begin
refresh materialized view mv1;
return null;
end $$;

For more information, see Materialized Views in the PostgreSQL documentation.