Indexed views like SQL Server - NOT Materialized Views
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.
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:
Show quoted text
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 ForumIdCREATE 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.
inspector morse <inspectormorse86@gmail.com> wrote:
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?
People are working toward such capabilities. While nobody can say
with any certainty when such features will make it into a
PostgreSQL release, I think it's safe to predict that it will not
be before late 2017, and most probably later than that.
This capability probably will be available through materialized
views, rather than using the SQL Server syntax.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2015-06-10 17:43 GMT+02:00 Kevin Grittner <kgrittn@ymail.com>:
inspector morse <inspectormorse86@gmail.com> wrote:
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?
People are working toward such capabilities. While nobody can say
with any certainty when such features will make it into a
PostgreSQL release, I think it's safe to predict that it will not
be before late 2017, and most probably later than that.This capability probably will be available through materialized
views, rather than using the SQL Server syntax.--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL CompanyHi,
Would views + partial indexes (based on views predicat) do the trick ?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi,
Would views + partial indexes (based on views predicat) do the trick ?
Nicolas Paris <niparisco@gmail.com> wrote:
Would views + partial indexes (based on views predicat) do the trick ?
I don't see anything promising that way, but feel free to work up a
proof of concept patch if you do.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general