6 ms·

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

soperj 5y ago
what are the downsides?
- cube2222 5y ago
  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.
  - topspin 5y ago
    > 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.
tqi 5y ago
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.
- dragonwriter 5y ago
  Aren't CTEs still optimization barriers in Postgres, so wouldn't that make any subquert optimization problem worse?
  - CBLT 5y ago
    Wasn't this solved in Postgres 12?[0] [0] https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
  - kristjansson 5y ago
    Mostly no, since PostgreSQL 12
  - deleted 5y ago
    [deleted]
  - srcreigh 5y ago
    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.
  - briffle 5y ago
    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.
  - yxhuvud 5y ago
    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.
- cube2222 5y ago
  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.
mijoharas 5y ago
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).
- cube2222 5y ago
  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!
  - mijoharas 5y ago
    I stand corrected, and always glad to see the main point about perf optimisation proven. (always measure! :) )
gaha 5y ago
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
deleted 5y ago
[deleted]
JoelJacobson 5y ago
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

soperj 5y ago

what are the downsides?

cube2222 5y ago
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.
- topspin 5y ago
  > 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.

tqi 5y ago

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.

dragonwriter 5y ago
Aren't CTEs still optimization barriers in Postgres, so wouldn't that make any subquert optimization problem worse?
- CBLT 5y ago
  Wasn't this solved in Postgres 12?[0] [0] https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
- kristjansson 5y ago
  Mostly no, since PostgreSQL 12
- deleted 5y ago
  [deleted]
- srcreigh 5y ago
  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.
- briffle 5y ago
  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.
- yxhuvud 5y ago
  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.
cube2222 5y ago
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.

mijoharas 5y ago

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).

cube2222 5y ago
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!
- mijoharas 5y ago
  I stand corrected, and always glad to see the main point about perf optimisation proven. (always measure! :) )

gaha 5y ago

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

deleted 5y ago

[deleted]

JoelJacobson 5y ago

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