Indexed views like SQL Server - NOT Materialized Views

Поиск
Список
Период
Сортировка
От inspector morse
Тема Indexed views like SQL Server - NOT Materialized Views
Дата
Msg-id CAHYn==6js1A5heMeCoSfJHH8pctfqT3UTMODE72NWrTeuU+S2A@mail.gmail.com
обсуждение исходный текст
Ответы Re: Indexed views like SQL Server - NOT Materialized Views
Re: Indexed views like SQL Server - NOT Materialized Views
Список 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.

В списке pgsql-general по дате отправления: