Created
February 14, 2017 17:51
-
-
Save encku/d912c67e422a75e8448b1535d18dc6e9 to your computer and use it in GitHub Desktop.
PostgreSQL 9.6 Views vs. Materialized Views Benchmark
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; | |
| -- Materialized view hook triggers | |
| CREATE OR REPLACE FUNCTION refresh_materialized_view() | |
| RETURNS TRIGGER | |
| LANGUAGE plpgsql | |
| AS $$ | |
| BEGIN | |
| REFRESH MATERIALIZED VIEW user_emails; | |
| RETURN null; | |
| END $$; | |
| CREATE TRIGGER materialized_view_invocation_with_users | |
| AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE | |
| ON users | |
| FOR EACH STATEMENT | |
| EXECUTE PROCEDURE refresh_materialized_view(); | |
| CREATE TRIGGER materialized_view_invocation_with_user_email_assignments | |
| AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE | |
| ON users | |
| FOR EACH STATEMENT | |
| EXECUTE PROCEDURE refresh_materialized_view(); | |
| BEGIN TRANSACTION; | |
| SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; | |
| -- Flush all tables | |
| DELETE FROM users CASCADE; | |
| ALTER SEQUENCE users_id_seq RESTART WITH 1; | |
| -- Generate series data | |
| INSERT INTO users (username, inserted_at) | |
| SELECT uuid_generate_v4() AS username, | |
| (timestamp '2014-01-10 20:00:00' + random() * (timestamp '2014-01-20 20:00:00' - timestamp '2014-01-10 10:00:00')) AS inserted_at | |
| FROM generate_series(1, 300000); | |
| INSERT INTO user_email_assignments(user_id, email, inserted_at) | |
| SELECT ((random() * 299997)::int + 3) user_id, | |
| md5(random()::text) email, | |
| (timestamp '2014-01-10 20:00:00' + random() * (timestamp '2014-01-20 20:00:00' - timestamp '2014-01-10 10:00:00')) AS inserted_at | |
| FROM generate_series(3, 420000) AS s; | |
| COMMIT TRANSACTION; | |
| CREATE INDEX CONCURRENTLY ON user_email_assignments(user_id); | |
| -- User emails view analysis | |
| -- #1. ID sub-sequence scan approach | |
| EXPLAIN ANALYZE VERBOSE | |
| SELECT u.id, u.username, uea1.email | |
| FROM user_email_assignments uea1 | |
| LEFT OUTER JOIN user_email_assignments uea2 ON (uea1.user_id = uea2.user_id AND uea1.id < uea2.id) | |
| INNER JOIN users u ON uea1.user_id = u.id | |
| WHERE uea2.id IS NULL; | |
| -- #2. Insertion time comparison (seq. scan major) | |
| EXPLAIN ANALYZE VERBOSE | |
| WITH user_latest_emails AS ( | |
| SELECT uea.user_id, max(uea.id) max_surrogate_id | |
| FROM user_email_assignments uea | |
| GROUP BY uea.user_id | |
| ) | |
| SELECT u.id, u.username, uea.email | |
| FROM users u | |
| LEFT OUTER JOIN user_latest_emails ule ON u.id = ule.user_id | |
| LEFT OUTER JOIN user_email_assignments uea ON uea.id = ule.max_surrogate_id; | |
| -- Canonical views based on optimizations | |
| CREATE VIEW user_emails AS | |
| WITH user_latest_emails AS ( | |
| SELECT uea.user_id, max(uea.id) max_surrogate_id | |
| FROM user_email_assignments uea | |
| GROUP BY uea.user_id | |
| ) | |
| SELECT u.id, u.username, uea.email | |
| FROM users u | |
| LEFT OUTER JOIN user_latest_emails ule ON u.id = ule.user_id | |
| LEFT OUTER JOIN user_email_assignments uea ON uea.id = ule.max_surrogate_id; | |
| CREATE MATERIALIZED VIEW user_emails AS | |
| WITH user_latest_emails AS ( | |
| SELECT uea.user_id, max(uea.id) max_surrogate_id | |
| FROM user_email_assignments uea | |
| GROUP BY uea.user_id | |
| ) | |
| SELECT u.id, u.username, uea.email | |
| FROM users u | |
| LEFT OUTER JOIN user_latest_emails ule ON u.id = ule.user_id | |
| LEFT OUTER JOIN user_email_assignments uea ON uea.id = ule.max_surrogate_id; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment