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" <inspectormors...@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. >
