2

In an effort to remove a caching layer from our application, I'm building a number of views to encapsulate the different ways a user can have access to a piece of content - one per "rule" of access. This is probably less efficient than making a single view with all of the rules built in, but it's much easier to think about everything this way.

Here's the main view:

CREATE VIEW access AS
SELECT c.id AS content_id, u.id AS user_id,
    LEAST(MIN(simple.relevant_date),
        MIN(with_prereq.relevant_date),
        MIN(with_delay.relevant_date),
        MIN(with_both.relevant_date)) AS relevant_date
FROM content AS c
    LEFT OUTER JOIN simple ON simple.content_id = c.id
    LEFT OUTER JOIN with_prereq ON with_prereq.content_id = c.id
    LEFT OUTER JOIN with_delay ON with_delay.content_id = c.id
    LEFT OUTER JOIN with_both ON with_both.content_id = c.id
    CROSS JOIN base_user AS u
WHERE u.id = COALESCE(
    simple.user_id, with_delay.user_id, with_prereq.user_id,
    with_both.user_id)
GROUP BY c.id, u.id;

Each of the "rule views" by themselves are fast, so I'm pretty sure the least efficient step is the u.id = COALESCE(... that I'm using to join. (The logic of relevant_date is bogus and irrelevant, but enough to prevent the answer of "just UNION them" from being trivial; I need to calculate various attributes differently depending on which rule is the source of access.) In any case, the end result needs to be a distinct set of content/user ids with some fiddly aggregates.

This is slow (~7s) on our (~10K users, ~10K content) data. What can I do to speed this up? I don't want to cache, I don't want to combine the "rule" views, and I couldn't figure out how to use UNION.

Here's the individual views that are pulled together by the above.

CREATE VIEW simple AS
SELECT cc.content_id AS content_id,
    cu.user_id AS user_id,
    cc.relevant_date AS relevant_date
FROM circle AS c
    JOIN circlecontent AS cc ON cc.circle_id = c.id
    JOIN circleuser AS cu ON cu.circle_id = c.id
WHERE (cc.delay IS NULL OR cc.delay = 0)
    AND cc.prereq_id IS NULL;

CREATE VIEW with_delay AS
SELECT cc.content_id AS content_id,
    cu.user_id AS user_id,
    cu.relevant_date AS relevant_date
FROM circle AS c
    JOIN circlecontent AS cc ON cc.circle_id = c.id
    JOIN circleuser AS cu ON cu.circle_id = c.id
WHERE (cc.delay IS NOT NULL AND cc.delay > 0)
    AND cc.prereq_id IS NULL;

CREATE VIEW with_prereq AS
SELECT cc.content_id AS content_id,
    cu.user_id AS user_id,
    GREATEST(cu.relevant_date, cc.relevant_date) AS relevant_date
FROM circle AS c
    JOIN circlecontent AS cc ON cc.circle_id = c.id
    JOIN circleuser AS cu ON cu.circle_id = c.id
WHERE (cc.delay IS NULL OR cc.delay = 0)
    AND cc.prereq_id IS NOT NULL;

CREATE VIEW with_both AS
SELECT cc.content_id AS content_id,
    cu.user_id AS user_id,
    LEAST(cu.relevant_date, cc.relevant_date) AS relevant_date
FROM circle AS c
    JOIN circlecontent AS cc ON cc.circle_id = c.id
    JOIN circleuser AS cu ON cu.circle_id = c.id
WHERE (cc.delay IS NOT NULL AND cc.delay > 0)
    AND cc.prereq_id IS NOT NULL;

Bonus: here's some minimal create statements to get this to be explainable:

CREATE TABLE content (
    id SERIAL
);
CREATE TABLE base_user (
    id SERIAL
);
CREATE TABLE circle (
    id SERIAL
);
CREATE TABLE circleuser (
    circle_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    relevant_date TIMESTAMP with time zone
);
CREATE TABLE circlecontent (
    circle_id INTEGER NOT NULL,
    content_id INTEGER NOT NULL,
    delay INTEGER,
    prereq_id INTEGER,
    relevant_date TIMESTAMP with time zone
);

Which should be enough to get EXPLAIN SELECT * FROM access WHERE user_id = 1 to show you the mess of operations.

  • 1
    Please show explain (buffers, verbose, analyze) output for the query in-situ with the real data. – Craig Ringer Apr 28 '15 at 1:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.