Optimize slow query when multiple JOIN LATERAL are present

Question

This is a follow-up optimization on Adding a SUM group by into a COUNT DISTINCT query (albeit with some optimizations and joins simplifications).

I wonder if it's possible to optimize the following PostgreSQL 13.1 query which took 130322.2ms to complete. Normally if only one JOIN LATERAL is present it does it in a few ms.

What I'm most lost is given that each JOIN LATERAL has an ON with a condition based on the score of its own subquery, how could I optimize the query potentially reducing the number of JOIN LATERAL and still get the same results.

From what I see, it seems it gets slow when conditional OR are added to some of the WHERE inside the JOIN LATERAL instead of AND. See:

SELECT count(*)
FROM subscriptions q
JOIN LATERAL (
      SELECT
        SUM(ts.score) AS sum_score
      FROM
        quiz_answers qa
        JOIN answers a ON a.id = qa.answer_id
        JOIN tag_scores ts ON ts.answer_id = a.id
      WHERE
        qa.quiz_id = q.quiz_id
        AND ts.tag_id = 21
    ) AS q62958 ON q62958.sum_score <= 1
JOIN LATERAL (
      SELECT
        SUM(ts.score) AS sum_score
      FROM
        quiz_answers qa
        JOIN answers a ON a.id = qa.answer_id
        JOIN tag_scores ts ON ts.answer_id = a.id
      WHERE
        qa.quiz_id = q.quiz_id
        OR ts.tag_id = 32
    ) AS q120342 ON q120342.sum_score <= 1
JOIN LATERAL (
      SELECT
        SUM(ts.score) AS sum_score
      FROM
        quiz_answers qa
        JOIN answers a ON a.id = qa.answer_id
        JOIN tag_scores ts ON ts.answer_id = a.id
      WHERE
        qa.quiz_id = q.quiz_id
        OR ts.tag_id = 35
    ) AS q992506 ON q992506.sum_score <= 1
JOIN LATERAL (
      SELECT
        SUM(ts.score) AS sum_score
      FROM
        quiz_answers qa
        JOIN answers a ON a.id = qa.answer_id
        JOIN tag_scores ts ON ts.answer_id = a.id
      WHERE
        qa.quiz_id = q.quiz_id
        OR ts.tag_id = 33
    ) AS q343255 ON q343255.sum_score <= 1
JOIN LATERAL (
      SELECT
        SUM(ts.score) AS sum_score
      FROM
        quiz_answers qa
        JOIN answers a ON a.id = qa.answer_id
        JOIN tag_scores ts ON ts.answer_id = a.id
      WHERE
        qa.quiz_id = q.quiz_id
        OR ts.tag_id = 29
    ) AS q532052 ON q532052.sum_score <= 1
JOIN LATERAL (
      SELECT
        SUM(ts.score) AS sum_score
      FROM
        quiz_answers qa
        JOIN answers a ON a.id = qa.answer_id
        JOIN tag_scores ts ON ts.answer_id = a.id
      WHERE
        qa.quiz_id = q.quiz_id
        OR ts.tag_id = 30
    ) AS q268437 ON q268437.sum_score <= 1
JOIN LATERAL (
      SELECT
        SUM(ts.score) AS sum_score
      FROM
        quiz_answers qa
        JOIN answers a ON a.id = qa.answer_id
        JOIN tag_scores ts ON ts.answer_id = a.id
      WHERE
        qa.quiz_id = q.quiz_id
        AND ts.tag_id = 46
    ) AS q553964 ON q553964.sum_score >= 3
JOIN LATERAL (
      SELECT
        SUM(ts.score) AS sum_score
      FROM
        quiz_answers qa
        JOIN answers a ON a.id = qa.answer_id
        JOIN tag_scores ts ON ts.answer_id = a.id
      WHERE
        qa.quiz_id = q.quiz_id
        AND ts.tag_id = 24
    ) AS q928243 ON q928243.sum_score >= 2
WHERE
  q.state = 'subscribed' AND q.app_id = 4
;

The subscriptions table has less than 15000 rows and less than 2000 matching the WHERE clause. Both q.state and q.app_id have indexes.

The complete EXPLAIN ANALYZE: https://explain.depesz.com/s/Ok0h

For performance questions, we need some basic details. Consider instructions here: https://stackoverflow.com/tags/postgresql-performance/info — Erwin Brandstetter, Nov 14 '21 at 21:12
Crucially important: How many rows in table `subscriptions` and roughly how selective is the filter `WHERE q.state = 'subscribed' AND q.app_id = 4`? — Erwin Brandstetter, Nov 14 '21 at 21:18
Please see updated question with updated query and link to EXPLAIN ANALYZE. — Martin, Nov 14 '21 at 21:34
Please edit your question to describe what you believe `WHERE qa.quiz_id = q.quiz_id OR ts.tag_id = ?` is doing. Without the data, schema, or a description of what it's meant to be doing, this just looks wonky. — MatBailie, Nov 14 '21 at 21:38

Erwin Brandstetter · Accepted Answer · 2021-11-14T23:47:57.700

The main issue is that the query is wrong:

 WHERE
            qa.quiz_id = q.quiz_id
            OR ts.tag_id = 32

The OR is misplaced here and must be introduced after aggregating the right rows. This WHERE clause includes all rows with either a matching quiz_id or tag_id = 32. So all rows, which is nonsense.

In addition to that, you can conflate multiple LATERAL subqueries with conditional aggregates like this:

SELECT count(*)
FROM   subscriptions q
JOIN   LATERAL (
   SELECT sum(ts.score) FILTER (WHERE ts.tag_id = 21) AS sum_score21
        , sum(ts.score) FILTER (WHERE ts.tag_id = 32) AS sum_score32
        , sum(ts.score) FILTER (WHERE ts.tag_id = 35) AS sum_score35
     -- , more?
   FROM   quiz_answers qa
   JOIN   answers      a  ON a.id = qa.answer_id
   JOIN   tag_scores   ts ON ts.answer_id = a.id
   WHERE  qa.quiz_id = q.quiz_id
   AND    ts.tag_id IN (21, 32, 35) -- more?
   ) AS t ON t.sum_score21 <= 1
          OR t.sum_score32 <= 1
          OR t.sum_score35 <= 1
       -- AND / OR more?
WHERE  q.state = 'subscribed'
AND    q.app_id = 4;

Be aware of operator precedence when adding more conditions with AND or OR: You may need parentheses as AND binds before OR.

About aggregated FILTER:

Aggregate columns with additional (distinct) filters

A multicolumn index on subscriptions(app_id, state, quizz_id) might help (give you index-only scans). But since the table isn't that big, that's not important.

LATERAL (rather than a plain subquery) still makes sense while the outer filter eliminates most rows from table subscriptions. A multicolumn index on tag_scores(answer_id, tag_id) might help.

With more tags in the subquery and/or more subscriptions, the LATERAL variant and the usefulness of said index fall off.

For comparison, here is a variant with plain subquery:

SELECT count(*)
FROM   subscriptions q
JOIN  (
   SELECT qa.quiz_id
        , sum(ts.score) FILTER (WHERE ts.tag_id = 21) AS sum_score21
        , sum(ts.score) FILTER (WHERE ts.tag_id = 32) AS sum_score32
        , sum(ts.score) FILTER (WHERE ts.tag_id = 35) AS sum_score35
     -- , more?
   FROM   quiz_answers qa
   JOIN   answers      a  ON a.id = qa.answer_id
   JOIN   tag_scores   ts ON ts.answer_id = a.id
   WHERE  ts.tag_id IN (21, 32, 35) -- more?
   GROUP  BY qa.quiz_id
   ) AS t USING (quiz_id)
WHERE  q.state = 'subscribed'
AND    q.app_id = 4
AND   (t.sum_score21 <= 1
    OR t.sum_score32 <= 1
    OR t.sum_score35 <= 1)
    -- AND / OR more?
;

Either way, t.sum_score21 <= 1 qualifies if ...

at least one row with tag 21 is associated
and the summed score of all with tag 21 for the same quiz is <= 1

Seems like a pretty narrow filter.

Denoise:

If the row in answers is never missing (referential integrity enforced with FK constraints?), you can cut out the middleman here:

   FROM   quiz_answers qa
   JOIN   answers      a  ON a.id = qa.answer_id
   JOIN   tag_scores   ts ON ts.answer_id = a.id

-->

   FROM   quiz_answers qa
   JOIN   tag_scores   ts USING (answer_id)

In that form it doesn't even need to be a lateral join. Just a plain inline-view / sub-query, grouped (and subsequently joined) by quiz_id. (But, I'm not sure this is what the OP wants, the question is lacking far too many details to be at all confident.) — MatBailie, Nov 14 '21 at 22:01
@MatBailie: Yep, it doesn't *need* to be. But - like noted above - while most rows of the main table are excluded early on (with the outer filter `WHERE q.state = 'subscribed' AND q.app_id = 4` LATERAL still makes sense, because the subquery can skip most quizzes this way. The OP might compare both and report the resulting performance to enlighten us ... — Erwin Brandstetter, Nov 14 '21 at 22:05
by the way, I don't see the added value of the `JOIN answers a ON a.id = qa.answer_id` ? Could be removed and just keep `FROM quiz_answers qa JOIN tag_scores ts ON ts.answer_id = qa.answer_id` ? — Edouard, Nov 14 '21 at 22:53
If you are interesting in the differences in performance: using `LATERAL` (https://explain.depesz.com/s/rAp2) took `57.308 ms` and using subquery (https://explain.depesz.com/s/PMzI) took `46.549 ms`. — Martin, Nov 14 '21 at 22:59
@Martin: Thanks for the feedback. Consider the added notes. With more rows being selected in the subquery, the `LATERAL` variant falls off. — Erwin Brandstetter, Nov 14 '21 at 23:33

Optimize slow query when multiple JOIN LATERAL are present

1 Answers1

Denoise:

Linked