56

You can't. The feature is disabled in 2017 RTM. That said, you can... Using AdventureWorks: CREATE VIEW dbo.TH WITH SCHEMABINDING AS SELECT P.ProductID, COUNT_BIG(*) AS cbs FROM Production.Product AS P JOIN Production.TransactionHistory AS TH ON TH.ProductID = P.ProductID GROUP BY P.ProductID; GO CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.TH (ProductID) ...


31

Looks like 9.3 and up you can do: select * from pg_matviews; select * from pg_matviews where matviewname = 'view_name'; More info found here: https://stackoverflow.com/questions/29297296/postgres-see-query-used-to-create-materialized-view


29

As you've noted, the view itself only materializes a small number of rows - so even if you update the whole table, the additional I/O involved with updating the view is negligible. You probably already felt the biggest pain you're going to feel when you created the view. The next closest will be if you add a gazillion rows to the base table with a bunch of ...


27

CAST is not deterministic because date format may change based on the server settings (i.e. under compatibility before 110 default date format is 0 => "Dec 12 2019 2:11PM" and output dependeds on language). To make it deterministic use CONVERT and the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic,...


26

Matching indexed views is a relatively expensive operation*, so the optimizer tries other quick and easy transformations first. If those happen to produce a cheap plan (0.05 units in your case) optimization ends early. The bet is that continued optimization would consume more time than it saved. Remember the optimizer's primary goal is a 'good enough' plan ...


25

This is assuming that materialized views have relpages >= 8 in pg_class, which doesn't have to be the case. It can actually be empty - not populated yet, indicated by pg_class.relispopulated = FALSE. The corresponding disk file has a zero size in this case. Try instead: SELECT relname AS objectname , relkind AS objecttype , reltuples AS ...


24

You can always implement your own table serving as "materialized view". That's how we did it before MATERIALIZED VIEW was implemented in Postgres 9.3. You can create a plain VIEW: CREATE VIEW graph_avg_view AS SELECT xaxis, AVG(value) AS avg_val FROM graph GROUP BY xaxis; And materialize the result once or whenever you need to start over: CREATE ...


23

I build indexed views in SQL Server all the time to tune existing products. The optimizer is smart enough to use the index if you are utilizing the appropriate columns. Using your example, it looks like you created the view but did not actually create an index upon it. if object_id(N'mytable1') is not null drop table mytable1 if object_id(N'mytable2') ...


22

This happens often if the optimiser thinks it can do better with the base tables. If I create an indexed view, I tend to always use NOEXPAND to make it use it. I also compare the query statistics to ensure that it adds some benefit. That is, if I identify a useful indexed view I want the optimiser to always use it. Example: SET STATISTICS IO ON; SET ...


22

The following explanation is given in this Microsoft Technical Article: Why does the first index on a view have to be CLUSTERED and UNIQUE? It must be UNIQUE to allow easy lookup of records in the view by key value during indexed view maintenance, and to prevent creation of views with duplicates, which would require special logic to maintain. It must be ...


20

There is no setting to change this behavior nor has it changed in newer SQL Server versions. An excerpt from the current documentation remarks section for SQL Server 2019 and Azure SQL Database: ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view


19

Aarons answers covered this question well. Two things to add: Aggregation indexed views can lead to cross-row contention and deadlocks. Normally, two inserts do not deadlock (except for rather rare conditions such as lock escalation or lock hash collisions). But if both inserts address the same group in the view they will contend. The same point stands for ...


18

As of PostgreSQL 9.4: Different to the documentation of CREATE VIEW, the documentation of CREATE MATERIALIZED VIEW does NOT mention the REPLACE keyword. There seems to be no shortcut aside from dropping all dependent objects and rebuilding each one. When you do so, I can only recommend two small things: Use DROP MATERIALIZED VIEW blabla CASCADE to get a ...


17

The query processor can produce an invalid execution plan for the (correct) query generated by DBCC to check that the view index produces the same rows as the underlying view query. The plan produced by the query processor incorrectly handles NULLs for the ImageObjectID column. It incorrectly reasons that the view query rejects NULLs for this column, when ...


17

You can do this with a function that runs in the security context of its owner. Function that refreshes the view (create it with the user that owns the MV/table): CREATE OR REPLACE FUNCTION refresh_mvw1() RETURNS void SECURITY DEFINER AS $$ BEGIN REFRESH MATERIALIZED VIEW mvw1 with data; RETURN; END; $$ LANGUAGE plpgsql; Grant execute on the function to ...


16

Concurrent Update (Postgres 9.4) While not an incremental update as you asked for, Postgres 9.4 does provide a new concurrent update feature. To quote the doc… Prior to PostgreSQL 9.4, refreshing a materialized view meant locking the entire table, and therefore preventing anything querying it, and if a refresh took a long time to acquire the exclusive ...


15

All three columns are persisted to disk in the clustered index on the indexed view (no different, really, from a clustered index on a regular table). You can validate this using DBCC PAGE. I created the following structure in tempdb: USE tempdb; GO CREATE TABLE dbo.a(a INT); CREATE TABLE dbo.b(a INT, b DATETIME); CREATE TABLE dbo.c(a INT, c CHAR(32)); ...


15

A view is just a "saved query". The indexes on the base table are still used whenever you access the view. You don't need to use an indexed view, unless the view contains an expensive logic (aggregations or joins) that you don't want to perform each time you query the view. Please note that even when the view is "materialized", the optimizer is free to ...


14

Unless a NOEXPAND hint is used, SQL Server always expands the view reference to the underlying stored query before optimization begins. It may later chose to match part(s) of, or the whole plan, back to one or more indexed views later on in the optimization process. There are two common reasons that the original expansion isn't reversed later: The plan ...


13

Materialized Views Do Not Exist in MySQL. Flexviews has been recommended in the DBA StackExchange before Bill Karwin's post : Does MySQL have a version of Change Data Capture? Redguy's post : Need some support on MySQL Query Since you have it already, some due diligence and elbow grease on your part may be necessary to get going on using it (if you haven't ...


13

Turns out this wasn't as complicated as I thought! (With just a little knowledge of pg_catalog...) Part 1: Query whether a materialized view exists: SELECT count(*) > 0 FROM pg_catalog.pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'm' AND n.nspname = 'some_schema' AND c.relname = 'some_mat_view'; Nice and easy. Part 2: ...


13

As mentioned in this answer, "REFRESH MATERIALIZED VIEW CONCURRENTLY takes an EXCLUSIVE lock" on the table. Following the crumb trail to documentation we can read that an EXCLUSIVE lock on a table "allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed". In the same paragraph we can see that "EXCLUSIVE conflicts with ... ...


12

It seems to ignore any index I put on it Unless you're using SQL Server Enterprise Edition (or equivalently, Trial and Developer), you will need to use WITH (NOEXPAND) on the view reference in order to use it. In fact, even if you are using Enterprise, there are good reasons to use that hint. Without the hint, the query optimizer (in Enterprise Edition) may ...


12

There is no INCREMENTAL, it's very simple.. From the docs REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name [ WITH [ NO ] DATA ] Perhaps you're confusing it with CONCURRENTLY which is about concurrency (locking) and not minimizing updates. Refresh the materialized view without locking out concurrent selects on the materialized view. Without this ...


11

Resolving indexes on Views: In SQL Server Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in the Standard edition or the Datacenter edition, the NOEXPAND table hint must be used. I'm guessing that you're on Standard Edition. Maybe you're falling foul of other rules listed on that page, such as: The ...


11

Short answer: You can't. Long answer: There's a few things going on here. First, you might be underestimating the power of XML AUTO. It will provide a certain amount of nesting "automatically". Your provided example, in fact, could be handled without the nested XML generation. Let's make some test tables and data: USE tempdb; DROP TABLE IF EXISTS T1; ...


11

As a_horse_with_no_name said in a comment: No, that's not possible. You need some kind of scheduler that runs refresh materialized view e.g. pg_cron or something on the operating system level – a_horse_with_no_name Alternatively, if you need a MATERIALIZED VIEW that refreshes when you run SELECT, just remove MATERIALIZED and use a regular VIEW. ...


11

You can prevent altering an indexed view with a DDL trigger. But the implementation is a bit complex, because the DDL trigger runs after the view has been altered and the index dropped, but before the change has been committed, and you can't directly detect that the view previously had an index. So you have to get up to some shenanigans with extended ...


9

So SET ARITHABORT ON basically says "if a divide by zero error happens or an arithmetic overflow happens abort the query" This is usually desirable behavior and is the default instance wide setting. If this causes issues with your vendor's queries, I would say that they may have been suffering from some coding issues to begin with. I would ask them for more ...


9

As someone said in the comments, pg_stat_statements is the way to get the statistics. Put this into your postgresql.conf: shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all Then run this query: CREATE EXTENSION pg_stat_statements; After that, this sample query (copied from the docs linked above)...


Only top voted, non community-wiki answers of a minimum length are eligible