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