Hacker News Reader

Comment by cube2222 | Hacker News Reader

Hey, author here.

The article describes a fun debugging and optimization session I’ve had recently. Happy to answer any questions!

Hey, I think you'll probably get better performance if you swap your subquery around a little from:

  SELECT COUNT(*)
  FROM runs runs_other
  WHERE (SELECT COUNT(*)
        FROM stacks
        WHERE stacks.id = runs_other.stack_id
          AND stacks.account_id = accounts.id
          AND stacks.worker_pool_id = worker_pools.id) > 0
   AND runs_other.worker_id IS NOT NULL

To:

  SELECT COUNT(*)
  FROM runs runs_other
  WHERE EXISTS (SELECT *
        FROM stacks
        WHERE stacks.id = runs_other.stack_id
          AND stacks.account_id = accounts.id
          AND stacks.worker_pool_id = worker_pools.id)
   AND runs_other.worker_id IS NOT NULL

Your previous option needs to hit every row in the stacks subquery to generate the count. The modified one uses EXISTS so it only needs to check there's at least one row, and then can short-circuit and exit that subquery. (a smart enough query planner would be able to do the same thing, but PG generally doesn't).

Thanks for the tip! I didn't know EXISTS can provide such benefits.

However, I've tried it out and it resulted in a 10x performance loss. Looking at the query plan, it actually got PostgreSQL to be too smart and undo most of the optimizations I've done in this blog post... It's still an order of magnitude faster than the original query though!

I stand corrected, and always glad to see the main point about perf optimisation proven. (always measure! :) )

Interesting write up! Did you happen to try this with a CTE instead of a subquery? ie something like:

  WITH
    tmp AS (SELECT accounts_other.id, COUNT(*) n
              FROM accounts accounts_other
              JOIN stacks stacks_other ON accounts_other.id = stacks_other.account_id
              JOIN runs runs_other ON stacks_other.id = runs_other.stack_id
             WHERE (stacks_other.worker_pool_id IS NULL OR
                    stacks_other.worker_pool_id = worker_pools.id)
               AND runs_other.worker_id IS NOT NULL
             GROUP BY 1
            )
  SELECT COUNT(*)                                                                  as "count",
         COALESCE(MAX(EXTRACT(EPOCH FROM age(now(), runs.created_at)))::bigint, 0) AS "max_age"
  FROM runs
    JOIN stacks ON runs.stack_id = stacks.id
    JOIN worker_pools ON worker_pools.id = stacks.worker_pool_id
    JOIN accounts ON stacks.account_id = accounts.id
    LEFT JOIN tmp ON tmp.id = accounts.id
  WHERE worker_pools.is_public = true
    AND runs.type IN (1, 4)
    AND runs.state = 1
    AND runs.worker_id IS NULL
    AND accounts.max_public_parallelism / 2 > COALESCE(tmp.n, 0)

I don't know this for sure, but in my experience it has seemed like Postgres is bad at optimizing subqueries, and will execute once per row.

Haven't run it, but based on the query plan in the first picture, PostgreSQL already does that part kind of right. It precalculates it for all accounts and then does a three-way hash join.

For Postgres 12+ you'll have to use to force the plan

> WITH tmp AS MATERIALIZED

Aren't CTEs still optimization barriers in Postgres, so wouldn't that make any subquert optimization problem worse?

The fun thing is, they have corrected a bunch of that in newer versions of postgresql.

I'm surprised that the problem stemmed from an index where the 'AND NOT NULL' took forevery to scan, and they didn't include a partial index, or an index in a different order since they are sorted.

Wasn't this solved in Postgres 12?[0]

[0] https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...

Depends if materialization helps.

As a silly example, if the CTE materializes 1/100th of a table via table scan, scanning 1/100th of a table N times may be faster than scanning the entire table N times.

That could even be a feature if used to compute just the list of active jobs. Later pgs might need to be explicit to reinsert the optimization barrier, as the implicit one for all Cates was removed.

Mostly no, since PostgreSQL 12

Have you tried unnesting the subquery? From the images posted in your blog it's not clear to me how often the subquery is run. Maybe unnesting it somehow like this should work well:

    SELECT COUNT(*)        as "count",
         COALESCE(MAX(EXTRACT(EPOCH FROM age(now(), runs.created_at)))::bigint, 0) AS "max_age"
    FROM runs
      JOIN stacks ON runs.stack_id = stacks.id
      JOIN worker_pools ON worker_pools.id = stacks.worker_pool_id
      JOIN accounts ON stacks.account_id = accounts.id
      /*unnesting*/
      join (SELECT accounts_other.id,  COUNT(*) as cnt
       FROM accounts accounts_other
         JOIN stacks stacks_other ON accounts_other.id = stacks_other.account_id
         JOIN runs runs_other ON stacks_other.id = runs_other.stack_id
       WHERE accounts_other.id
         AND (stacks_other.worker_pool_id IS NULL OR
          stacks_other.worker_pool_id = worker_pools.id)
         AND runs_other.worker_id IS NOT NULL
        group by accounts_other.id) as acc_cnt 
      on acc_cnt.id = accounts.id and  accounts.max_public_parallelism / 2 > acc_cnt.cnt
     /*end*/
    WHERE worker_pools.is_public = true
      AND runs.type IN (1, 4)
      AND runs.state = 1
      AND runs.worker_id IS NULL
      /* maybe also copy these filter predicates inside the unnested query above */

Edit: format and typo

Hi Jacob, I would like to try rewriting your query using different JOIN FOREIGN [1] syntax alternatives, to study how readability is affected.

It would help if you could share the table definitions and foreign keys for the tables involved in the query. I could probably deduce most of it from the query thanks to proper naming of tables/columns, but just want to be sure I get it right. Many thanks!

    [1]: https://gist.github.com/joelonsql/15b50b65ec343dce94db6249cfea8aaa

what are the downsides?

The downside is that a PostgreSQL version update can change internal optimizer rules, which may result in the plan being changed to a less performant one again.

In that case it's good to have monitoring in place to catch something like that. We're using Datadog Database monitoring which gives you a good breakdown of what the most pricey queries are - it uses information from the pg_stat_statements table, which contains a lot of useful stats about query execution performance.

You should regularly check such statistics to see if there aren't any queries which are unexpectedly consuming a big chunk of your database resources, as that might mean you're missing an index, or a non-optimal query plan has been chosen somewhere.

> The downside is that a PostgreSQL version update can change internal optimizer rules

This has actually happened relatively recently.

PostgreSQL Common Table Expressions (WITH x as (some query)) used to be optimization fences by default, meaning that you can influence the optimizer using a CTE. This is a well known technique among PostgreSQL users that resort to it for lack of optimizer hints.

In Pg12 they enhanced the optimizer to remove the optimization fence by default, so the query plans of existing queries automatically changed and sometimes became much slower as a result. If you want the old CTE behavior you have to modify the WITH clause via AS MATERIALIZED.

Reply on news.ycombinator.com