If the underlying table/view of the MV is dropped, is the MV also dropped? In a way. If a view has a dependency, such as a materialized view, an attempt to simply DROP VIEW would fail with: ERROR: cannot drop view v_test because other objects depend on it DETAIL: materialized view mv_test depends on view v_test HINT: Use DROP ... CASCADE to drop the dependent objects too. If you then take the hint and do DROP VIEW .. CASCADE, all dependencies (and their dependencies) will also be dropped.

The horrible estimation of 'transfers' condition (expected 2600, found 1) caused the cost of the hash join and the nested loop to appear falsely similar. Why does misestimating by 2600 fold cause one query to use a different plan but not the other? No idea, probably just luck. I doubt it is even reproducible, much less interesting. Fix the root problem. If an ANALYZE transfers doesn't fix it, then you will need to dig into pg_stats for column "from_name".

Interval should be trunc(sysdate)+1 to mean midnight tomorrow. Your next_date will be evaluated to midnight today (in the past) so the first refresh will happen ASAP. You probably want this to also be tomorrow midnight

You could write a procedure for that: CREATE PROCEDURE some_name(...) LANGUAGE plpgsql AS $$BEGIN /* do all the inserts */ COMMIT; REFRESH MATERIALIZED VIEW ...; END;$$; The same can of course also be written using client code. This may lock, but never deadlock, and you can be certain that the materialized view will reflect the inserts when the procedure has completed.

Naming conventions are largely a question of taste and style. The one important thing is to stay consistent. An inconsistent naming convention is often worse than none at all. To give my opinion: don't add any prefix or suffix. Especially since you intend to switch later. And always stick to legal, lower-case names. So you never have to double-quote. See: https://stackoverflow.com/a/20880247/939860

As trigger cannot be set on Foreign Table, I've find an other way by using https://github.com/citusdata/pg_cron

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)); INSERT dbo.a SELECT 1; INSERT dbo.b SELECT 1, SYSDATETIME(); INSERT dbo.c SELECT 1, REPLICATE('c', 32); GO CREATE VIEW dbo.v WITH SCHEMABINDING AS SELECT a.a, b.b, c.c FROM dbo.a INNER JOIN dbo.b ON a.a = b.a INNER JOIN dbo.c ON a.a = c.a; GO CREATE UNIQUE CLUSTERED INDEX x ON dbo.v(a); GO Turned a trace flag on so I can use IND/PAGE: DBCC TRACEON(3604, -1); DBCC IND('tempdb', 'dbo.v', 1); Results: So I knew to check page 312: DBCC PAGE(2, 1, 312, 3); And here are the three values from the join, even though they're not all part of the unique aspect of the clustered index:

A fast-refreshable materialized view cannot contain a non-deterministic function like current_timestamp. So if you want to materialize the data from the last 24 hours in a materialized view, the materialized view would need to do a complete refresh every time. Do you need a materialized view? Could you maintain your own staging table and create a custom job that runs every few minutes, deletes the data that is now more than 24 hours old, and inserts the new data (either by directly querying the table or creating a trigger that writes the new data to a different table)? That's likely a non-starter if you need to use the query rewrite functionality of a materialized view but if you just want the benefit of having a smaller table to query, custom code may be more efficient. Or could you partition the table by day so that queries for the past 24 hours always just have to hit the two most recent partitions?

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 details on why they are concerned here. Of all of the rules of indexed views, I would call this and many of the set options rules the least controversial. This would have to be set in the connections that interact with the view. So you would want to work with the vendor and really try and understand their reasoning and try and get them to commit to what they are thinking on the big disagreement here. That said - Indexed views are a bit of a big deal. They have other rules and they can impact the application and assumptions the vendor's developers had when building and performance testing. You should really have a conversation with them about the business problem you are trying to solve through indexed views and get them involved in the conversation about how to solve the problem.

Typically you index views if you are often running aggregates, not to magically speed up joins. Also if you are not using table partitioning there is no good reason to investigate partition-aligned indexed views. You should be focusing on optimizing the query, irrespective of the view, IMHO. An indexed view is not a magic turbo button (though a lot of people seem to think it is). Also consider whether you should just run a query separately from the view anyway - often people run a lot of queries through the same view for convenience, even if the view actually touches more columns / rows than are necessary for the specific query.

No, the reading of your cats, dogs and humans tables wont block other processes that are inserting in them.

I can't understand why you can't aggregate on the fly and why it's so slow. Is the "Valid" idea a workaround to deal with the lag of OrderTotals or some business process Both of these ideas discard the InvalidOrder table which is a workaround for poor indexing. Suggestion 1: Create a computed column ALTER TABLE dbo.Order ADD PriceXQuantity AS Price * Quantity PERSISTED Add an index CREATE INDEX IX_Totals ON dbo.Order (OrderID) INCLUDE Quantity, PriceXQuantity) See what happens Suggestion 2: Use an Indexed view CREATE VIEW OrderTotals WITH SCHEMABINDING AS select OrderId = r.OrderId , TotalQty = SUM(r.Quantity) , TotalGrossConsid = SUM(r.Price * r.Quantity) , COUNT_BIG(*) AS Dummy from dbo.Order r group by r.OrderId GO CREATE UNIQUE CLUSTERED INDEX IXCU_OrderTotals ON OrderTotals (OrderId9 GO You can use the computed column here too

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 quickly. Using the clustered index on the view isn't expensive in itself, but the process of matching a logical query tree to potential indexed views can be. As I mentioned in a comment on the other question, the view reference in the query is expanded before optimization, so the optimizer doesn't know you wrote the query against the view in the first place - it sees only the expanded tree (as if the view had been in-lined). "Good Enough Plan" means the optimizer found a decent plan and stopped early in an exploration phase. "TimeOut" means it exceeded the number of optimization steps it set itself as a 'budget' at the start of the current phase. The budget is set based on the cost of the best plan found in a previous phase. With such a low-cost query (0.05) the number of budgeted moves will be quite small, and quickly exhausted by regular transformation given the number of joins involved in your sample query (there are lots of ways to rearrange inner joins, for example). If you are interested to know more about why indexed view matching is expensive, and therefore left for later stages of optimization and/or only considered for more costly queries, there are two Microsoft Research Papers on the topic here (pdf) and here (citeseer). Another relevant factor is that indexed view matching is not available in optimization phase 0 (transaction processing). Further reading: Indexed Views and Statistics * and only available in Enterprise Edition (or equivalent)

All your assumptions are correct. You need to ANALYZE the materialized view to get correct estimates – this should be done automatically by autovacuum, so I am not sure what could be wrong there. It also looks like your materialized view is terribly bloated. You would need to run VACUUM (FULL) on it to fix that. Both point in the direction of autovacuum not doing its job properly. You should investigate that.

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 estimated cost for using the index has the lowest cost of any access mechanisms considered by the query optimizer. Like any query on a view, the reference to the view in the query is expanded into the view definition before optimization. If the optimizer finds a cheap enough way to satisfy the query before it considers matching the query tree to indexed views, you get a plan that accesses the base tables. WITH (NOEXPAND) is the only way to guarantee the optimizer produces a plan that accesses an index on a view directly.

ALTER TABLE ... SWITCH would allow you to switch in each table when you want to change the "live" table. The only requirement is to ensure both tables involved in the switch are in the same filegeoup, and have the same structure.

I tested in my test server ambient and worked. SQL> create table test_mv (col1 number not null unique, col2 number); Table created. SQL> create materialized view log on test_mv with primary key; Materialized view log created. SQL> create materialized view mv refresh fast on demand as 2 select col1,col2, sdo_geometry(2001, 26917, sdo_point_type(col1,col2, null), null, null) as shape 3 from test_mv; Materialized view created. SQL> insert into test_mv values (1, 1); 1 row created. SQL> insert into test_mv values (2, 2); 1 row created. SQL> commit; Commit complete. SQL> exec dbms_mview.refresh('MV', 'f'); PL/SQL procedure successfully completed. col1 col2 shape 1 1 (2001, 26917, (1, 1, ), (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ), (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , )) 2 2 (2001, 26917, (2, 2, ), (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ), (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ))

I have the Quantity indexed on both tables because I obviously wish to sort and filter on this column, but of course the index doesn't help if I want to sort and filter on vwThingAugmented. Based on your view and the comment on filtering I am going to assume queries like this: SELECT * FROM dbo.vwThingAugmented WHERE Quantity = 5; Where there is a filter operator late in the execution plan: One of the things you could try, depending on the size of the tables and the type of filters you do would be to create a view with a UNION an INNER JOIN and a NOT EXISTS instead of a LEFT JOIN CREATE VIEW dbo.vwThingAugmented_2 AS SELECT t.ID , Quantity = t.Quantity + a.Quantity , AdditionGroupID FROM dbo.Thing t INNER JOIN dbo.AdditionGroup a ON t.AdditionGroupID = a.ID UNION ALL SELECT t.ID , Quantity = t.Quantity , AdditionGroupID FROM dbo.Thing t WHERE NOT EXISTS (SELECT * FROM dbo.AdditionGroup a WHERE t.AdditionGroupID = a.ID); You do get two accesses to dbo.Thing and dbo.AdditionGroup in this case, YMMV depending on the type of query you run against the view. You could try running the same query again: SELECT * FROM dbo.vwThingAugmented_2 WHERE Quantity = 5; This removes the filter operator but you do have double index access with a concatenate. And you still have residual predicates on both parts of the query, Because the nonclustered index is not covering: DROP INDEX IX_Thing_Quantity ON dbo.Thing; CREATE INDEX IX_Thing_Quantity ON dbo.Thing (Quantity, ID) INCLUDE (AdditionGroupID); Where now the bottom part is improved to use the nonclustered index (without a key lookup) instead of the clustered index : Lastly, as mentioned by you, you could add an indexed view to the first part of the view. Remember to use NOEXPAND for best performance, even in Enterprise edition.

There is no such thing as a "nested table" in Postgres, but it seems you just want a JSON representation of the group assignments for each user. The following query does this: select u.user_id, u.title, jsonb_build_object( 'user_id', u.user_id, 'title', u.title, 'groups', jsonb_agg(jsonb_build_object('group_id', g.group_id, 'user_type', m.user_type))) as groups from members m join users u on u.user_id = m.user_id join groups g on g.group_id = m.group_id group by u.user_id, u.title; Given your sample data, the above returns: user_id | title | groups --------+--------+----------------------------------------------------------------------------------------------------------------------------------------- U_1 | User 1 | {"title": "User 1", "groups": [{"group_id": "G_1", "user_type": "admin"}, {"group_id": "G_2", "user_type": "admin"}], "user_id": "U_1"} U_2 | User 2 | {"title": "User 2", "groups": [{"group_id": "G_1", "user_type": "member"}, {"group_id": "G_3", "user_type": "admin"}], "user_id": "U_2"} Online example

This works as you want on Enterprise/Developer Edition: use tempdb go CREATE TABLE dbo.T (id int PRIMARY KEY, b bit NOT NULL, txt varchar(20)); GO CREATE OR ALTER VIEW dbo.V WITH SCHEMABINDING AS SELECT T.Id, T.txt FROM dbo.T AS T WHERE T.b = 1; GO CREATE UNIQUE CLUSTERED INDEX idx_V ON dbo.V (Id); GO UPDATE V SET txt = 'Foo'; The update has this plan running Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) Sep 23 2020 16:03:08 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19042: ) (Hypervisor) You can add a feedback item here to request that NOEXPAND be supported on UPDATE queries. I put in a PR to update the docs to clarify that NOEXPAND is not available on UPDATE.