Дослідження швидкодії складеного первинного ключа в Postgres, MySQL та CockroachDB

CREATE TABLE user_favorite_companies_v1
(
    id             SERIAL,
    user_id        INT                      NOT NULL,
    company_id     INT                      NOT NULL,
    created_at     TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at     TIMESTAMP WITH TIME ZONE NOT NULL,
    favorite_state BOOLEAN                  NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (user_id, company_id),
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (company_id) REFERENCES companies (id)
);

CREATE TABLE user_favorite_companies_v2
(
    user_id        INT                      NOT NULL,
    company_id     INT                      NOT NULL,
    created_at     TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at     TIMESTAMP WITH TIME ZONE NOT NULL,
    favorite_state BOOLEAN                  NOT NULL,
    PRIMARY KEY (user_id, company_id),
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (company_id) REFERENCES companies (id)
);

        

CREATE TABLE user_favorite_companies_v1
(
    id             INT AUTO_INCREMENT,
    user_id        INT      NOT NULL,
    company_id     INT      NOT NULL,
    created_at     DATETIME NOT NULL,
    updated_at     DATETIME NOT NULL,
    favorite_state BOOLEAN  NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (user_id, company_id),
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (company_id) REFERENCES companies (id)
) ENGINE = InnoDB;

CREATE TABLE user_favorite_companies_v2
(
    user_id        INT      NOT NULL,
    company_id     INT      NOT NULL,
    created_at     DATETIME NOT NULL,
    updated_at     DATETIME NOT NULL,
    favorite_state BOOLEAN  NOT NULL,
    PRIMARY KEY (user_id, company_id),
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (company_id) REFERENCES companies (id)
) ENGINE = InnoDB;

        

\timing

SET SESSION profiling_history_size = 100;

SET SESSION cte_max_recursion_depth = 1000000;

SET SESSION profiling = 1;

TRUNCATE user_favorite_companies_v1;

TRUNCATE user_favorite_companies_v1;

INSERT INTO user_favorite_companies_v1 (
       user_id,
       company_id,
       created_at,
       updated_at,
       favorite_state
)
SELECT generate_series,
       1 + (0 + generate_series * 20) % 10000,
       '2022-11-05 12:00:00',
       '2022-11-05 12:00:00',
       TRUE
FROM generate_series(1, 1000 * 1000)
ON CONFLICT (user_id, company_id) DO UPDATE
    SET updated_at     = '2022-11-05 12:00:00',
        favorite_state = TRUE;

INSERT INTO user_favorite_companies_v1 (
       user_id,
       company_id,
       created_at,
       updated_at,
       favorite_state
)
SELECT number,
       1 + (0 + number * 20) % 10000,
       '2022-11-05 12:00:00',
       '2022-11-05 12:00:00',
       TRUE
FROM (
       WITH RECURSIVE numbers AS (
           SELECT 1 AS number
           UNION ALL
           SELECT number + 1
           FROM numbers
           WHERE number < 1000 * 1000
       )
       SELECT *
       FROM numbers
) AS generate_series
ON DUPLICATE KEY UPDATE
       updated_at     = '2022-11-05 12:00:00',
       favorite_state = TRUE;

SELECT COUNT(*)
FROM user_favorite_companies_v1
WHERE (user_id, company_id) IN (
    SELECT generate_series,
           1 + (0 + generate_series * 20) % 10000
    FROM generate_series(1, 1000 * 1000)
);

SELECT COUNT(*)
FROM user_favorite_companies_v1
WHERE (user_id, company_id) IN (
    SELECT number, 1 + (0 + number * 20) % 10000
    FROM (
             WITH RECURSIVE numbers AS (
                 SELECT 1 AS number
                 UNION ALL
                 SELECT number + 1
                 FROM numbers
                 WHERE number < 1000 * 1000
             )
             SELECT *
             FROM numbers
         ) AS generate_series
);

SHOW PROFILES;