Skip to main content

Questions tagged [materialized-view]

Defined like a view but holding persistent data like a table, Materialized Views are a feature of a number of RDBMSs including Oracle, DB2 and postgres. SQL Server has a similar feature called an Indexed View that is considered similar enough to come under this tag.

Filter by
Sorted by
Tagged with
5 votes
2 answers
605 views

Indexed view - Cannot create index because "select list of the view contains an expression on result of aggregate function or grouping column."

I have a fairly simple view I'm trying to index. CREATE OR ALTER VIEW [schema].[IndexedView] WITH SCHEMABINDING AS SELECT SUM(ISNULL([Quantity], 0)) as [Quantity], [Address], [Asset], ...
deadwards's user avatar
  • 113
0 votes
1 answer
83 views

Sharding a Materialized View

I am a Postgres 15.3 AWS RDS instance and have a database that centers around one table, called posts that has 20 other relations to build the full object. The full query is about a second altogether ...
Frank Conry's user avatar
1 vote
2 answers
241 views

Does dropping a Postgres table cascade to materialized views that depend on it?

I understand that a regular view will be invalid if a table it is dependent on is dropped. Does the same apply to a materialized view? I would assume that in a materialized view since the data has ...
vfclists's user avatar
  • 1,053
1 vote
0 answers
59 views

Continious aggregates (keeping aggregated data in a fresh state) for PostgreSQL

I build a PostgreSQL database for movie db platform. This DB is heavy loaded for readings of many related data. Here is simplified schema what I have: create table items ( id ...
Dmitry K.'s user avatar
  • 143
0 votes
0 answers
14 views

Detect Delete/Insert/Update in MatView Refresh Concurently

I have a matview which I refresh once in a hour.(50M rows). By running refresh materialized view concurently my_view; Note - MatView contains a unique Index too, on column unique_id. I have a table ...
Nikhil Badyal's user avatar
0 votes
0 answers
60 views

Materialized view creation times out after too many creation/deletion with the same name

I was trying to create a MV on a Postgres db, on a table with a few millions records. During the development process, I migrated & rolled back (CREATE MATERIALIZED VIEW / DROP MATERIALIZED VIEW) a ...
Antoine's user avatar
  • 101
0 votes
0 answers
71 views

Postgres 13: Update column when MV refresh is complete

I have what feels like a very simple operation I wish to perform, but cannot find any guidance. I have an application that allows users to upload a series of files with records, which get processed ...
Kevin H's user avatar
0 votes
0 answers
47 views

Storing Aggregation pipeline inside materialized view in Mongo

I am quite new to Mongo Coming from Sql background. I have a aggregation on collection_1 which involves $graphlookup (given below) [ { $match: { $or: [ { objStatus: "...
Arun's user avatar
  • 13
0 votes
1 answer
220 views

Slow count on empty table unexplained

My incremental materialized view refresh starting to become slower and slower. Each day there are millions of new records. So there are many deletes on the mv log table. After investigation I see one ...
Nir's user avatar
  • 533
0 votes
0 answers
330 views

Refreshing materialized view blocks an unrelated table

Last few days I was experiencing a problem that seems really strange to me and even though I have some experience with PostgreSQL, I really don't understand what may be the cause. We have two services ...
Paweł Sopel's user avatar
0 votes
1 answer
275 views

ORA-12015 materialized view complex query error with simple join two table

I have 2 simple table which has columns like : table1 : "OBJECTID" NUMBER NOT NULL ENABLE "OLCEK_ID" NUMBER(*,0) table2 : "OBJECTID" NUMBER NOT NULL ENABLE i created ...
CartWheel's user avatar
4 votes
4 answers
499 views

Is there a way to disable logging / backups for view indexes?

We have a SQL Server database that uses indexed views to allow for efficient querying of some data that changes regularly (yes I know there's a cost to maintaining the index when the data changes but ...
ChaseMedallion's user avatar
1 vote
1 answer
604 views

Should we reconsider using materialized views in Cassandra?

There is a bit of talk lately around the use of materialized views (MV) in Cassandra. As an ordinary user, it is difficult to know whether it is or it isn't a good idea to use MVs. What is the ...
Erick Ramirez's user avatar
3 votes
0 answers
60 views

How does a where clause in a view affect how the db engine processed DML with respect to indexed views? [duplicate]

Imagine a database where you have an indexed view aggregating data from 2 tables, a dataset table and a datapoints table (master/detail). When a dataset is first created it has a status of LOAD. The ...
Aheho's user avatar
  • 151
1 vote
1 answer
198 views

Time based incremental load query optimization and index design

Problem I try to optimize an extract/load query on a highly normalized table structure. Extract/load is implementing incremental loading using a datetime column to determine only records which have ...
Clemens K's user avatar
2 votes
1 answer
370 views

Error while creating continuous aggregate (materialized view) when source and new time columns have the same name (PSQL/TSDB)

I'm facing a problem when creating a continuous aggregate on timescaledb (based on materialized views) when the source time column and the new time column have the same name like below: CREATE ...
Héctor's user avatar
  • 297
3 votes
1 answer
2k views

ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function

I need to execute CREATE MATERIALIZED VIEW ... WITH DATA from a function/procedure but I'm getting ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function. There is any ...
Héctor's user avatar
  • 297
0 votes
2 answers
426 views

Unable to Create Fast Refresh Materialized View with Geometry Field

I am receiving the following error while trying to create a materialized view with a geometry field on Oracle (21c): ORA-12015: cannot create a fast refresh materialized view from a complex query I ...
salgado's user avatar
1 vote
0 answers
101 views

PostgreSQL trigger for statement explanation

PostgreSQL 14 I try to understand the behaviour of trigger for each statement. Here is an example : AFTER INSERT OR DELETE OR UPDATE ON mytbl FOR EACH STATEMENT EXECUTE FUNCTION ...
Leehan's user avatar
  • 207
9 votes
1 answer
3k views

How does SQL Server update indexed views?

Every time you insert, update, or delete data in the base table(s) that the indexed view references, the database engine will automatically update the indexed view to reflect the changes. However, I'm ...
Fajela Tajkiya's user avatar
0 votes
0 answers
206 views

Materialized view slow with Timescale

I have a table created like this: -- enable timescale plugin CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE; -- create schema CREATE SCHEMA IF NOT EXISTS {schemaName}; -- create table CREATE ...
Thomas's user avatar
  • 303
0 votes
0 answers
501 views

Refresh materialized views upon database restore

Our database (PG 12) uses a few materialized views, some depending on others. We use pg_dump for backups and psql to restore. And, we do this restore every once in a while, for example when upgrading ...
vektor's user avatar
  • 401
0 votes
0 answers
30 views

Is calling a stable function performance comparable to reading values from a table?

Let's say we have a function _test_has_group_feature, which is meant to check if a group (identified by a name) has a particular feature (identified by name). _test_has_group_feature(group_name, ...
Gajus's user avatar
  • 1,195
0 votes
1 answer
47 views

How can I sort thousands of Replies on a Post, following an algorithm that's not just based on columns of the "Replies" table?

On my website, Replies to Post are sorted based on an algorithm that depends on multiple factors: Reactions, Reply Quality, User Reputation, Reports, Replies to Reply, etc... (these are just a few ...
Nuno's user avatar
  • 829
0 votes
0 answers
48 views

Is there a formalism for the kinds of constraints we can implement with indexed-views? Or at least a rule-of-thumb?

SQL Server’s Indexed Views provide a performant way to implement certain classes of database-scoped constraints - such as ensuring a value is unique over multiple tables - or other non-key, cross-...
Dai's user avatar
  • 592
0 votes
1 answer
64 views

Help Troubleshooting Full-text Search Function Performance

Query finds posts with a 10 mile-radius of postal code. Response time is much too high Number of rows in the PG Materialized Table: 520,000 Any help would be appreciated. Query: EXPLAIN ANALYZE SELECT ...
Guy's user avatar
  • 1
1 vote
1 answer
126 views

Do missing index DMVs work with indexed views?

I've been delving into query store to find the cause of some high CPU usage and found some potential index improvements to some existing indexed views by looking through the plans of the highest CPU ...
Inverted Llama's user avatar
1 vote
1 answer
66 views

Improving postgreSQL 13.3 performance on a materialised view?

I found one of our APIs using this query in our materialised view which generates aggregated report on asset inventory (PostgreSQL 13.3) and is very slow on a table with 1.2 million rows SELECT ...
gigz's user avatar
  • 111
1 vote
1 answer
1k views

Postgres Materialized Views keeps getting dropped every time underlying views is updated

I am using Postgres as my DB and I have a bunch of Materialized Views that are created from a view. The MVs are in Schema B and Views are in Schema A. They are created like create SchemaB.mv1 as ...
user16573033's user avatar
1 vote
2 answers
907 views

Why does Postgres not use an index when joining a materialized view?

I have a table defined as: CREATE TABLE addresses ( "address" varchar(42), "other_info1" text, "other_info2" bigint ); and I have some manual corrections to ...
henryperson's user avatar
1 vote
2 answers
136 views

Problem with Oracle Job Scheduling

I have a job that refreshes a materialised view refresh group on Oracle 19c. I wanted to refresh the group daily at midnight - trunc(sysdate)? The job was made using using the following PL/SQL: ...
Huskie69's user avatar
  • 137
1 vote
0 answers
598 views

Refresh materialized view on Postgresql 11 on RDS

We are currently on Postgres 11.13 on AWS RDS. I am trying to create a materialized view that takes about 6-7 minutes to run. What is the best way to keep this MV mostly up to date? I was thinking of ...
Asif's user avatar
  • 111
0 votes
1 answer
424 views

postgresql : conflict with simultaneous "create materialized view"

This is a continuation on my old question Multiple concurrent "REFRESH MATERIALIZED VIEW": how to manage? Now I'm unfortunately struggling with the real thing after having been more ...
wazoox's user avatar
  • 115
4 votes
1 answer
4k views

Naming conventions for views and materialized views: "*_v", "v_*", "*_mv", "mv_*" or none? [closed]

My view stores quality metrics for various gizmos1 in the database. Today I designed it to be a view. In a few years, as we produce more gizmos, it may need to be changed to a materialized view. I was ...
Timur Shtatland's user avatar
0 votes
0 answers
1k views

Postgres materialized view: no performance gain

I've created a materialized view to cache results of joining about 15 tables. Unfortunately, select * from mv_cache takes the same amount of time as the original joins. Am I doing anything wrong? ...
Dmitry Kurkin's user avatar
0 votes
1 answer
675 views

PostgreSQL : Refresh a materialized view base on a foreign table

I've got a foreign table from a distant csv host on a external webserver. I need to enhance this table so I create a materialized view to add some extra fields and join with internal data. Making a ...
Bad Wolf's user avatar
0 votes
0 answers
211 views

Materialized view creates quickly with data but cannot refresh

I am working with a database on Amazon RDS and have a specific query which joins about 6 tables on keys. Creating the materialised view with data takes about 3 minutes and behaves as expected with ...
Cong Chen's user avatar
1 vote
1 answer
717 views

List Postgres materialized views the current user has select grant

With select matviewname from pg_matviews I get all materialized views in the database. How can I filter the result to get only those the current user has select grant (= read permission) on?
JohnDoe's user avatar
  • 113
4 votes
1 answer
236 views

Indexed view maintenance with foreign key not elided when inserting parent row

TL;DR; Given an indexed view that has a JOIN between two tables that already have a foreign key relationship along with a WHERE predicate on the parent table. When inserting into the parent table of ...
Charlieface's user avatar
  • 13.3k
1 vote
1 answer
233 views

Oracle multithreaded JDBC bulk select high-performance data export

The goal of our solution is to export as fast as possible CLOB data from an events table. The concept is to logically partition the data with a partition_id column such that every thread from the ...
d.sndrs's user avatar
  • 11
2 votes
1 answer
450 views

postgresql materialized view ERROR: a negative number raised to a non-integer power yields a complex result

I have complex view with calculations but one line makes issue ROUND((((latest_revenue::numeric/ oper_reve_3y_old::numeric) ^ (1/3::numeric)) -1::numeric)*100::numeric) When I execute the query, ...
Mi Ro's user avatar
  • 123
0 votes
0 answers
1k views

Oracle mview refresh taking long time

the customer I work for is used to launch a script to refresh mviews on its db. Refresh command for all mviews is issued in the following way: DBMS_MVIEW.refresh('"OWNER"."MVIEW"','...
trustno1's user avatar
0 votes
0 answers
1k views

How can I exclude Materialised Views from a pg_dump

I'm doing a pg_dump of an AWS RDS database so I can use it for local development, but there are some large tables I want to exclude. I've successfully removed them using the --exclude-table=blah* flag ...
Kathy Rindhoops's user avatar
1 vote
3 answers
6k views

Quering millions of rows is still slow with index and a materialized view

The system has 4 tables that are joined to get a lot of data about users, this query was turned into a view with 37 columns and a total of ~8 million rows. Eventually this became slow due to a user ...
Chessbrain's user avatar
  • 1,213
0 votes
1 answer
2k views

Postgres metadata: get a list of all MATERIALIZED VIEW columns, with data type, INCLUDING array columns

I need to get metadata about all MATERIALIZED VIEW columns, including their data type. I can do this with this query: SELECT trim(leading '_' from pg_type.typname) as data_type, pg_namespace....
LaVache's user avatar
  • 323
0 votes
1 answer
24 views

List sorted Posts on Group and children Groups efficiently

Say I have a system that has Groups and Posts on those Groups. A Group can have multiple "child Groups" Group 1 > Group 2 > Group 3 Group 1 > Group 2 > Group 4 Group 1 > ...
Nuno's user avatar
  • 829
6 votes
1 answer
2k views

Is vacuuming a PostgreSQL materialized view necessary?

I understand it makes sense to analyze a Materialized View (MV), but since MVs are not updateable, does that imply that we need not ever vacuum a MV?
MichaelDBA's user avatar
0 votes
0 answers
430 views

Convert jsonb column into view for Grafana

I have a partially dynamic data that I have to save into Postgres 13 and then visualize it in Grafana. Example for one document insert (partial) { "Timestamp": { "$date": { ...
ivans's user avatar
  • 11
1 vote
1 answer
1k views

Need help understanding Postgres materialized views under the hood

postgres 9.6.20 Need some help getting an accurate mental model of whats happening with postgres materialized views. Based on this from postgres docs (https://www.postgresql.org/docs/9.6/rules-...
meeech's user avatar
  • 113
2 votes
3 answers
94 views

Preserving full refresh data source

I have a SQL Server database that gets updated every week with a completely new set of data. I am currently using two staging databases. A series of view databases are connecting to these and one week ...
user3546025's user avatar

1
2 3 4 5 6