Skip to content

Instantly share code, notes, and snippets.

@encku

encku/psql-9.6-views-benchmark

Created February 14, 2017 17:51
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save encku/d912c67e422a75e8448b1535d18dc6e9 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save encku/d912c67e422a75e8448b1535d18dc6e9 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. encku created this gist Feb 14, 2017.
    92 changes: 92 additions & 0 deletions psql-9.6-views-benchmark
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,92 @@
    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;