#materialized views

[psddude]

MySQL Materialized Views

I've been putting together a complete guide on how to create a MySQL materialized view and here are the 4 methods that I found so far: Using triggers - you can set triggers on the source tables on which you build the view. This minimizes the resource usage as the refresh is only done when needed.

[banker-hacker]

pinpointed bottleneck, and it’s materialized view

this query is taking way too long. i think because it involves a materalized view:

SELECT "mv_project_data".* FROM "mv_project_data" INNER JOIN "projects" ON "projects"."id" = "mv_project_data"."project_id" INNER JOIN "productions" ON "productions"."project_id" = "projects"."id" INNER JOIN "sheets" ON "sheets"."production_id" = "productions"."id" WHERE "sheets"."id" = 12 LIMIT 1

UPDATE actually it's not the database. the db calls are only a total of 5ms of a 1 second call. the db call is 1/200 of the total. the other is coming from the app layer

[bdarfler]

Old (check the section on PDAs) but still a good overview on the subject.

[banker-hacker]

difference primary key vs unique key in #sql for materialized views?

http://docs.oracle.com/cd/E11882_01/server.112/e10706/repmview.htm#i30768

http://www.dba-oracle.com/rep_primary_key_based_mv.htm

there’s got to be a better way than this.  this article says you need to create the matview with a primary key; delete the row with the primary key; then replace the primary key with the key from your table that should be there.  wow.

man i just spent the entire morning on this, and the answer is so simple and obvious.  i suspected it would be a definition in the activerecord model.

[banker-hacker]

create an index for materialized view in @postgresql 9.3+

official pg 9.4 doc

http://www.postgresql.org/docs/9.4/static/sql-createindex.html

http://michael.otacoo.com/postgresql-2/postgres-9-4-feature-highlight-refresh-concurrently-a-materialized-view/

oracle

http://www.dba-oracle.com/t_materialized_view_refresh_method_benchmark.htm

http://www.postgresonline.com/journal/archives/313-Caching-data-with-materialized-views-and-statement-level-triggers.html

also resequence your rows based on your matview_index for increased performance.  to the contrary, another post says:

It would only make sense to want the data in a heap table to be physically ordered with the primary key index if you are doing a range scan of the primary key index and then doing a single row lookup in the table. That tends to be relatively uncommon-- you don't commonly want to fetch all the data for ORDER_ID 1-100 from the ORDER table, for example.

which actually sounds like a common scenario contrary to author’s opinion--after all, isn’t this what a activerecord `where` lo-oks like?

based on the discussion above, seems indexing the matview is sufficient, and re sequencing it doing too much.

The two most common operations on a materialized view are query execution and fast refresh, and each operation has different performance requirements. Query execution might need to access any subset of the materialized view key columns, and might need to join and aggregate over a subset of those columns. Consequently, query execution usually performs best if a single-column bitmap index is defined on each materialized view key column.

can you throw an index on an array column?

[banker-hacker]

find_each failing on UUIDs

trying to use find_each on materialized view and it’s failing; could be due to this:

https://groups.google.com/forum/#!topicsearchin/compositekeys/after$3A2011$2F06$2F01$20before$3A2011$2F06$2F30

http://seejohncode.com/2011/09/16/uuids-and-find-in-batches/

[banker-hacker]

pg_restore stuck on create materialized view

http://postgresql.nabble.com/Failure-loading-materialized-view-with-pg-restore-td5838393.html

pg_restore --verbose -a --no-acl --no-owner -h localhost -d libval_development ~/Desktop/20150311.dump

the -a means only restore data

used pg_dumpall -s to see log of the process

this didn't work; my schema had been changed, and an association didn't work (couldn't find primary key of a particular table). so i'm doing a full clean restore again. it's stuck on matviews again at 15 min so far. don't know how long i should give it.

[banker-hacker]

yes lawd

It’s Time To Get Over That Stored Procedure Aversion You Have