Indexed views like SQL Server - NOT Materialized Views

From: inspector morse <inspectormorse86(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Indexed views like SQL Server - NOT Materialized Views
Date: 2015-06-09 23:27:06
Message-ID: CAHYn==6js1A5heMeCoSfJHH8pctfqT3UTMODE72NWrTeuU+S2A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

SQL Server has a feature called Indexed Views that are similiar to
materialized views.

Basically, the Indexed View supports COUNT/SUM aggregate queries. You
create a unique index on the Indexed View and SQL Server automatically
keeps the COUNT/SUM upto date.

Example:
CREATE VIEW ForumTopicCounts
AS
SELECT ForumId, COUNT_BIG(*) AS TopicsCount
FROM Topics
GROUP BY ForumId

CREATE UNIQUE CLUSTERED INDEX idx ON ForumTopicCounts(ForumId);

After doing that, if you add or delete a topic from the Topics Table, SQL
Server automatically keeps the count updated.....and it's fast because of
the unique index.

Doing the same thing in Postgresql using Materialized views is slow and the
developer has to manually issue a "refresh materialized view" command. The
alternative is to write additional sql to update count
columns....uneccessary work.

Do you know when Postgresql will implement such a feature? Counting is
already slow in Postgresql, adding similiar feature like SQL Server will
really help.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message William Dunn 2015-06-10 06:56:30 Re: Indexed views like SQL Server - NOT Materialized Views
Previous Message Bruce Momjian 2015-06-09 23:00:24 The purpose of the core team