Обсуждение: 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. 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.
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:
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.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.CREATE UNIQUE CLUSTERED INDEX idx ON ForumTopicCounts(ForumId);GROUP BY ForumIdFROM TopicsSELECT ForumId, COUNT_BIG(*) AS TopicsCountASCREATE VIEW ForumTopicCountsExample: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.
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.
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
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