Re: Indexed views like SQL Server - NOT Materialized Views

Though I'm sure you've already looked into it, for your specific issue of
getting row counts:
- In PostgreSQL 9.2 and above this operation can be made much faster with
index-only scans so ensure you are on a recent version and do your count on
a column of a candidate key with an index (for example, the primary key)
- An approximate rowcount is maintained in pg_stat_user_tables, if an
approximate value is acceptable you can obtain one there very fast

As for PostgreSQL implementing Microsoft SQL Server features:
In general, Microsoft SQL Server is famous for it's lack of standards
compliance while PostgreSQL is famously ANSI/ISO standards compliant. If a
SQL Server non-standard feature is not adopted by Oracle and/or DB2 and/or
the standards it is unlikely PostgreSQL will adopt it unless the feature is
very highly desired or a contributor has a deep interest. However it is
more likely for non-standard features to be implemented as a PostgreSQL
plug-in.
On Jun 9, 2015 7:28 PM, "inspector morse" <inspectormorse86(at)gmail(dot)com>
wrote:

> 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.
>