23 ms·

Tricking PostgreSQL into using an insane, but faster, query plan

259 points by cube2222 5y ago

cube2222 5y ago
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]
    - 5y ago
- 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
victor106 5y ago
From the article > Aurora Serverless also doesn’t support read replicas to offload analytical queries to them, so no luck there either. Amazon Aurora Serverless v2 does support read replicas. Although it’s still in preview.
- notatoad 5y ago
  aurora serverless v2 doesn't support postgres at all...
SigmundA 5y ago
PostgreSQL really needs hints. Sometimes I as the developer know better than the optimizer and having to trick the optimizer to get it to do what I want is a ridiculous situation. Also having the optimizer decide to do something bad off hours is not a good situation again hints or plan locking would help here.
- baq 5y ago
  even plan locking itself would be a welcome feature. we've had multiple instances of plans going haywire (never traced it down, but 99% sure it was after autovacuum analyze) impacting prod and getting back to normal after a manual re-ANALYZE.
  - SigmundA 5y ago
    I haven't had much use for plan locking even after MS SQL server got it, even though its a nice feature with being able save load plans from a file etc. I just like good set of hints since they are part of source code and I can put comments and see why they are there in source history later.
  - zmmmmm 5y ago
    if nothing else I would love to have that just so we don't end up debugging heisenbugs that occur and then disappear when you try to find them (or we can't rule it out when we test the same query and it magically starts performing well).
- jonatron 5y ago
  Having been in a situation where Postgres suddenly decides to use a much slower query plan on a hot query in production, I'd agree.
- electroly 5y ago
  I really appreciate that in SQL Server we can just use query hints. I don't think I will ever see eye-to-eye with the PostgreSQL team on this. No query planner is perfect. SQL Server's planner certainly isn't. We have had the same sort of "query plan suddenly changes in production and everything breaks" incidents, but they are easily fixed with query hints and/or forced plans, and you can be sure that future statistics updates won't break them again.
pkrumins 5y ago
Instead of doing this madness with complicated queries, I always advise to split complicated queries into many smaller simple queries and then merge the results in the code. This way you don't need to debug the queries as all of them are very simple SELECTs and the code that merges the results is one or two for loops.
- cube2222 5y ago
  That's more custom logic and additional code to maintain though. In this case we're using the Datadog Agent with its custom_queries directive to automatically create metrics based on periodically evaluated SQL queries.
- Tostino 5y ago
  That can sometimes be a solution... Just keep in mind, you are manually writing a join algorithm every time you need to do that, and sometimes it will be the right one for the task, other times not so much. The query planner will adapt your join type for you instead, if you can trust it. As the shape of your data changes over time, your hard coded algorithm choice is not guaranteed to be the best one.
- ccleve 5y ago
  This. Plus, you can use temporary tables, which limits the amount of application code you have to write. Put your intermediate results in temp tables, and build indexes on them if you have to. It's a cheap way of forcing the optimizer to do things your way.
- cultofmetatron 5y ago
  in practice that means mroe data being sent to the application code that then has to process that. 99% of the time, you'll get more performance figuring out the right way to express the data structure you want in sql. The entire computation happens in the database so all the data is immediately available to process on disk and you only send what you need for bandwidth consumption. IF the query is slow, you need to think about how you setup your joins and being aggressive about your use of predicates in subqueries to reduce the size of temporary tables.
deltaonefour 5y ago
This article is everything that's wrong with SQL. How about just providing us access to the query plan and allowing users to directly modify it? It's like needing assembly language to optimize a really tight section of code but having that access blocked off. Why do we have to rely on leakage to a higher level interface in order to manipulate the lower level details? Just give us manual access when we need it.
- shock-value 5y ago
  I think SQL is generally great but I do agree with you. Access to lower level primitives from which one could construct their own query plan would be awesome. Query hints (not available in Postgres) would also probably get you a lot of the way there.
slt2021 5y ago
There is no need to trick query optimizer, just common sense and understanding of rdbms engine. The query seems pretty standard and often occuring problem with nested queries. Every JOIN and nested query grows computation due to cartesian combination, so having your predicates as narrow and specific as possible is a must. Also Nested Loop is a kiss of death for your query performance
srcreigh 5y ago
The curious part is why postgres can't estimate that the size of the active runs<>stacks join will be very small. A core part of query planning is estimating the best join order using a dynamic programming algorithm. So roughly, A(1M) <> B(1M) <> C(10) should use the join order A-(B-C), not (A-B)-C. I bet it's something like, postgres doesn't know the correlation between runs_other.worker_id and runs_other.stack_id. It seems like its seeing low number of runs_other.worker_id, then estimating the stacks are split evenly among those small number of runs in the millions. (Why it wouldn't know stacks.id correlates to one stack each, idk. Is stacks.id not the primary key? Is there a foreign key from runs? Very curious.) What happens if you follow this guide to hint Postgres as to which columns are statistically related? [0] Hinting PG's estimating stats may be a more persistent solution. Another core part of query planning is query rewriting-if they add a rule to simplify COUNT(select...)>0 to normal joins (which are equivalent I think?) then your trick may stop working. [0]: https://www.postgresql.org/docs/12/multivariate-statistics-examples.html https://www.postgresql.org/docs/12/multivariate-statistics-e...
- cube2222 5y ago
  To be fair, we're using Aurora Serverless, so I'll have to dig deeper if we can use these kinds of hints. But thanks for the tip, this could be really useful!
  - mst 5y ago
    Given Aurora talks about itself as a replacement for pg/mysql that provides compatible skims I'm honestly wondering whether you're dealing with the pg optimiser here in any meaningful way. (exactly how far along the scale from 'tweaked version' to 'basically just a skin on a completely new database' aurora is is ... opaque to me ... there's probably something out there that answers that but I haven't yet managed to find it) Edited to add: zorgmonkey downthread points out they have support for some extensions like pg_hint_plan - https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Maintenance.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide... - which suggests at least a decent amount of pg code but I'm still curious exactly where the changes start.
Justsignedup 5y ago
heh i had a similar style problem, i think it was more 1000x than 200x, but its because certain query structures the optimizer couldn't figure out, had to try like 4 different re-writes of the sql till the optimizer could understand it. I guess at this point I just see it as just part of my job to do this insane stuff, good to know that others see this as hard :)
hnov 5y ago
In this case denormalizing the data right into the runs table would likely give you better, but more importantly more predictable performance.
no-s 5y ago
It’s impressive how many developer misconceptions I see cropping up in these articles. The point of the query planner is to replace an army of programmers, not evade design faults. Using a server RDBMS it’s trivially easy to decouple schema and query evolution from application. Yet somehow developers always seem to back themselves into situations requiring extreme coupling. Using plan guides in the MSSQL environment helps somewhat in patchwork solutions, tho typically the deficiencies are usually related to obsolete statistics…(and that’s not even necessary). In Postgres you should ensure design separation so you can rewrite the query easily.
- newlisp 5y ago
  Using a server RDBMS it’s trivially easy to decouple schema and query evolution from application. Curious, how do you decouple query evolution from the DB layer of your application?
  - dragonwriter 5y ago
    > Curious, how do you decouple query evolution from the DB layer of your application? Use application-specific views, and the evolution driven by DB optimization, etc., can happen in view definitions without the app DB layer being involved. It’s a traditional best practice anyway, going back decades to the time when it was expected an RDBMS would serve multiple apps that you needed to keep isolated from each others changes, but it also works to isolate concerns at different levels for a single-app stack. (Because of practical limits of views in some DBs, especially in the 1990s, and developer preferences for procedural code, a common enterprise alternative substitutes stored procs for views, to similar effect.)
    - newlisp 5y ago
      Oh yes views, they are not with no effort and become numerous and specific. But are extremely missed with a document store I'm working on write now ;)
    - no-s 5y ago
      hah, exactly. “application-specific views...Going back decades”, or “substitute Stored Procs for views”. It’s not really much extra overhead to use a schema+views. I ask developers to avoid creating views with “Select *”, always specify the columns desired. Further decoupling is possibly beneficial, e.g. CQRS is wonderful perspective for designing data models that may be more easily distributed or cacheable (by explicitly choosing to separate query schema from modification schema).
AaronFriel 5y ago
Regarding this: > Only a minuscule part of the Runs in the database are active at any given time. Most Stacks stay dormant most of the time. Whenever a user makes a commit, only the affected Stacks will execute Runs. There will obviously be Stacks that are constantly in use, but most won’t. Moreover, intensive users will usually use private Worker Pools. The public shared Worker Pool is predominantly utilized by smaller users, so that’s another reason for not seeing many Runs here. > Right now, we’re iterating over all existing Stacks attached to the public worker pool and getting relevant Runs for each. Is there an opportunity to make this query more robust with a bit of denormalization, if necessary, to indicate which runs and which stacks are active and a filtered index (index with a WHERE clause) on each to allow efficiently retrieving the list of active entries? Best practices for database optimization tell you "don't index on low cardinality columns" (columns with few unique values), but the real advice should be "ensure each valid key of an index has a low cardinality relative to the size of the data" (it's OK to have an index that selects 1% of your data!). That is, conventional wisdom says that indexing on a boolean is bad, because in the best case scenario 50% of your entries are behind each key, and in the worst case almost all entries are behind either "true" or "false". There are performance impacts from that and there's the risk the query optimizer tries to use the "bad" side of the index, which would be worse than a full table scan. But with the improved advice and insight that 0.01% of rows have value "true", you can create an index with a `WHERE active = true` clause, et voila: you have an index that finds you exactly what you want, without the cost and performance issues of maintaining the "false" side of the index.
- cube2222 5y ago
  Yep, you could indeed use a partial index with the two relevant conditions to solve this query well. We're using those all over the place as well.
deleted 5y ago
[deleted]
_xnmw 5y ago
Are you happy with Aurora Serverless so far? Is it really the "painless scaling" it's made out to be? I'm really hesitant to use anything other than self-hosted Postgres with full control, because I'm always afraid of running into situations like these and not being able do what I need to do, like have an offline analytical read replica. Also I imagine the cost is an order of magnitude more than a self-hosted instance.
- cube2222 5y ago
  Not really. But it's mostly because the support for other features is basically non-existant. It also seems like all development is happening on Aurora Serverless v2 (which I think is in preview state?). The scaling also isn't seamless, it breaks your connections if you're using prepared statements - we have it pinned to a specific number of capacity units. We'll probably move to plain Aurora at some point, as that supports all bells and whistles, but for now Aurora Serverless is good enough.
Mathnerd314 5y ago
Similar article that instead solved it by recording more statistics: https://build.affinity.co/how-we-used-postgres-extended-statistics-to-achieve-a-3000x-speedup-ea93d3dcdc61?gi=2c35fecd14f8 https://build.affinity.co/how-we-used-postgres-extended-stat...
unnouinceput 5y ago
Whenever I see "select count(star_sign)" I shake my head. The first optimization you learn as a newby dabbling in SQL is to always use "select count(some_fucking_id)" and not "star_sign". Who wrote this query should be fired, because it has 2 of this problem, one in a subquery!
- cube2222 5y ago
  That's harsh, could you explain why, please? As far as I understand (and I'm the author) `COUNT(*)` means "count the number of rows, regardless of the values". `COUNT(id)` means "count all non-NULL id's" which ends up being the same for non-nullable columns, but requires additional processing for nullable ones. Thus the first option should be able to optimize out more processing time in some situations and at worst be the same. This is confirmed by some quick googling, it's also confirmed by me running the query with a COUNT(*) and COUNT(id) - both run in the same amount of time, and it's also how it works in a SQL engine[0] I've implemented - COUNT(*) is able to optimize out the most underlying data loading. [0]:https://github.com/cube2222/octosql https://github.com/cube2222/octosql
  - unnouinceput 5y ago
    Yeah, if you have the ID's as null. But why you would do such thing in the first place? Have the ID be "serial primary key" and you'll never have null values in there ever! Btw, that's the second optimization you learn. So you telling me that in your case, you have null values in your ID's? You should be fired trice then. Since you're hell bent on doing tests, do one with a dozen millions rows, just ID and a varchar as columns, and in one case have the ID have nulls, and in the other case be like I said. Then see the difference when you run "select count(ID) where the_varchar_column = 'Steve'" vs "select(star_sign) bla bla" and come back to me. Image would be appreciated. Then go rewrite your DB structure and the query when you realize the performance
    - option_greek 5y ago
      I'm not sure what value your harsh tone adds to your comment. Sharing experiences like this publicly benefits everyone. It obviously exposes blindspots in one's knowledge and it is expected that noone knows everything. That is the point of the. Comments functionality in HN. So that we can all share things that we know and absorb things we don't. I personally enjoyed this article and learnt a few things from it. I learnt a few things from your comment too but it would have been better without all the harsh words.
JoelJacobson 5y ago
Deduced schema based on the naming of tables/columns: CREATE TABLE accounts ( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY, max_public_parallelism integer NOT NULL, PRIMARY KEY (id) ); CREATE TABLE worker_pools ( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY, is_public boolean NOT NULL, PRIMARY KEY (id) ); CREATE TABLE stacks ( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY, worker_pool_id bigint NOT NULL, account_id bigint NOT NULL, PRIMARY KEY (id), FOREIGN KEY (worker_pool_id) REFERENCES worker_pools, FOREIGN KEY (account_id) REFERENCES accounts ); CREATE TABLE runs ( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY, stack_id bigint, worker_id bigint, created_at timestamptz NOT NULL DEFAULT now(), type integer NOT NULL, state integer NOT NULL, PRIMARY KEY (id), FOREIGN KEY (stack_id) REFERENCES stacks );
- JoelJacobson 5y ago
  cube222, does the schema above look more or less correct to you? Below are the query plans I got when running the query against empty tables in PostgreSQL 15dev (not Aurora Serverless), for comparison. Would be nice to populate the tables and add indexes, to make a real comparison. Would help a lot with some rough estimates on the number of rows in each table and count(distinct ...) of each column, etc? Unoptimized version: QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=82.14..82.16 rows=1 width=16) (actual time=0.009..0.012 rows=1 loops=1) -> Hash Join (cost=46.05..82.13 rows=1 width=8) (actual time=0.005..0.007 rows=0 loops=1) Hash Cond: (stacks.worker_pool_id = worker_pools.id) Join Filter: ((accounts.max_public_parallelism / 2) > (SubPlan 1)) -> Nested Loop (cost=0.30..36.38 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=1) -> Nested Loop (cost=0.15..36.18 rows=1 width=24) (actual time=0.004..0.005 rows=0 loops=1) -> Seq Scan on runs (cost=0.00..28.00 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1) Filter: ((worker_id IS NULL) AND (type = ANY ('{1,4}'::integer[])) AND (state = 1)) -> Index Scan using stacks_pkey on stacks (cost=0.15..8.17 rows=1 width=24) (never executed) Index Cond: (id = runs.stack_id) -> Index Scan using accounts_pkey on accounts (cost=0.15..0.20 rows=1 width=12) (never executed) Index Cond: (id = stacks.account_id) -> Hash (cost=32.00..32.00 rows=1100 width=8) (never executed) -> Seq Scan on worker_pools (cost=0.00..32.00 rows=1100 width=8) (never executed) Filter: is_public SubPlan 1 -> Aggregate (cost=66.89..66.90 rows=1 width=8) (never executed) -> Nested Loop (cost=33.72..66.89 rows=1 width=0) (never executed) -> Hash Join (cost=33.56..58.71 rows=1 width=8) (never executed) Hash Cond: (runs_other.stack_id = stacks_other.id) -> Seq Scan on runs runs_other (cost=0.00..22.00 rows=1194 width=8) (never executed) Filter: (worker_id IS NOT NULL) -> Hash (cost=33.55..33.55 rows=1 width=16) (never executed) -> Seq Scan on stacks stacks_other (cost=0.00..33.55 rows=1 width=16) (never executed) Filter: (((worker_pool_id IS NULL) OR (worker_pool_id = worker_pools.id)) AND (account_id = accounts.id)) -> Index Only Scan using accounts_pkey on accounts accounts_other (cost=0.15..8.17 rows=1 width=8) (never executed) Index Cond: (id = accounts.id) Heap Fetches: 0 Optimized version: QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=82.14..82.16 rows=1 width=16) (actual time=0.010..0.012 rows=1 loops=1) -> Hash Join (cost=46.05..82.13 rows=1 width=8) (actual time=0.006..0.007 rows=0 loops=1) Hash Cond: (stacks.worker_pool_id = worker_pools.id) Join Filter: ((accounts.max_public_parallelism / 2) > (SubPlan 2)) -> Nested Loop (cost=0.30..36.38 rows=1 width=28) (actual time=0.005..0.006 rows=0 loops=1) -> Nested Loop (cost=0.15..36.18 rows=1 width=24) (actual time=0.005..0.005 rows=0 loops=1) -> Seq Scan on runs (cost=0.00..28.00 rows=1 width=16) (actual time=0.004..0.005 rows=0 loops=1) Filter: ((worker_id IS NULL) AND (type = ANY ('{1,4}'::integer[])) AND (state = 1)) -> Index Scan using stacks_pkey on stacks (cost=0.15..8.17 rows=1 width=24) (never executed) Index Cond: (id = runs.stack_id) -> Index Scan using accounts_pkey on accounts (cost=0.15..0.20 rows=1 width=12) (never executed) Index Cond: (id = stacks.account_id) -> Hash (cost=32.00..32.00 rows=1100 width=8) (never executed) -> Seq Scan on worker_pools (cost=0.00..32.00 rows=1100 width=8) (never executed) Filter: is_public SubPlan 2 -> Aggregate (cost=9851.00..9851.01 rows=1 width=8) (never executed) -> Seq Scan on runs runs_other (cost=0.00..9850.00 rows=398 width=0) (never executed) Filter: ((worker_id IS NOT NULL) AND ((SubPlan 1) > 0)) SubPlan 1 -> Aggregate (cost=8.18..8.19 rows=1 width=8) (never executed) -> Index Scan using stacks_pkey on stacks stacks_1 (cost=0.15..8.18 rows=1 width=0) (never executed) Index Cond: (id = runs_other.stack_id) Filter: ((account_id = accounts.id) AND (worker_pool_id = worker_pools.id))

259 points by cube2222 5y ago

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]
  - 5y ago
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

victor106 5y ago

From the article > Aurora Serverless also doesn’t support read replicas to offload analytical queries to them, so no luck there either. Amazon Aurora Serverless v2 does support read replicas. Although it’s still in preview.

notatoad 5y ago
aurora serverless v2 doesn't support postgres at all...

SigmundA 5y ago

PostgreSQL really needs hints. Sometimes I as the developer know better than the optimizer and having to trick the optimizer to get it to do what I want is a ridiculous situation. Also having the optimizer decide to do something bad off hours is not a good situation again hints or plan locking would help here.

baq 5y ago
even plan locking itself would be a welcome feature. we've had multiple instances of plans going haywire (never traced it down, but 99% sure it was after autovacuum analyze) impacting prod and getting back to normal after a manual re-ANALYZE.
- SigmundA 5y ago
  I haven't had much use for plan locking even after MS SQL server got it, even though its a nice feature with being able save load plans from a file etc. I just like good set of hints since they are part of source code and I can put comments and see why they are there in source history later.
- zmmmmm 5y ago
  if nothing else I would love to have that just so we don't end up debugging heisenbugs that occur and then disappear when you try to find them (or we can't rule it out when we test the same query and it magically starts performing well).
jonatron 5y ago
Having been in a situation where Postgres suddenly decides to use a much slower query plan on a hot query in production, I'd agree.
electroly 5y ago
I really appreciate that in SQL Server we can just use query hints. I don't think I will ever see eye-to-eye with the PostgreSQL team on this. No query planner is perfect. SQL Server's planner certainly isn't. We have had the same sort of "query plan suddenly changes in production and everything breaks" incidents, but they are easily fixed with query hints and/or forced plans, and you can be sure that future statistics updates won't break them again.

pkrumins 5y ago

Instead of doing this madness with complicated queries, I always advise to split complicated queries into many smaller simple queries and then merge the results in the code. This way you don't need to debug the queries as all of them are very simple SELECTs and the code that merges the results is one or two for loops.

cube2222 5y ago
That's more custom logic and additional code to maintain though. In this case we're using the Datadog Agent with its custom_queries directive to automatically create metrics based on periodically evaluated SQL queries.
Tostino 5y ago
That can sometimes be a solution... Just keep in mind, you are manually writing a join algorithm every time you need to do that, and sometimes it will be the right one for the task, other times not so much. The query planner will adapt your join type for you instead, if you can trust it. As the shape of your data changes over time, your hard coded algorithm choice is not guaranteed to be the best one.
ccleve 5y ago
This. Plus, you can use temporary tables, which limits the amount of application code you have to write. Put your intermediate results in temp tables, and build indexes on them if you have to. It's a cheap way of forcing the optimizer to do things your way.
cultofmetatron 5y ago
in practice that means mroe data being sent to the application code that then has to process that. 99% of the time, you'll get more performance figuring out the right way to express the data structure you want in sql. The entire computation happens in the database so all the data is immediately available to process on disk and you only send what you need for bandwidth consumption. IF the query is slow, you need to think about how you setup your joins and being aggressive about your use of predicates in subqueries to reduce the size of temporary tables.

deltaonefour 5y ago

This article is everything that's wrong with SQL. How about just providing us access to the query plan and allowing users to directly modify it? It's like needing assembly language to optimize a really tight section of code but having that access blocked off. Why do we have to rely on leakage to a higher level interface in order to manipulate the lower level details? Just give us manual access when we need it.

shock-value 5y ago
I think SQL is generally great but I do agree with you. Access to lower level primitives from which one could construct their own query plan would be awesome. Query hints (not available in Postgres) would also probably get you a lot of the way there.

slt2021 5y ago

There is no need to trick query optimizer, just common sense and understanding of rdbms engine. The query seems pretty standard and often occuring problem with nested queries. Every JOIN and nested query grows computation due to cartesian combination, so having your predicates as narrow and specific as possible is a must. Also Nested Loop is a kiss of death for your query performance

srcreigh 5y ago

The curious part is why postgres can't estimate that the size of the active runs<>stacks join will be very small. A core part of query planning is estimating the best join order using a dynamic programming algorithm. So roughly, A(1M) <> B(1M) <> C(10) should use the join order A-(B-C), not (A-B)-C. I bet it's something like, postgres doesn't know the correlation between runs_other.worker_id and runs_other.stack_id. It seems like its seeing low number of runs_other.worker_id, then estimating the stacks are split evenly among those small number of runs in the millions. (Why it wouldn't know stacks.id correlates to one stack each, idk. Is stacks.id not the primary key? Is there a foreign key from runs? Very curious.) What happens if you follow this guide to hint Postgres as to which columns are statistically related? [0] Hinting PG's estimating stats may be a more persistent solution. Another core part of query planning is query rewriting-if they add a rule to simplify COUNT(select...)>0 to normal joins (which are equivalent I think?) then your trick may stop working. [0]: https://www.postgresql.org/docs/12/multivariate-statistics-examples.html https://www.postgresql.org/docs/12/multivariate-statistics-e...

cube2222 5y ago
To be fair, we're using Aurora Serverless, so I'll have to dig deeper if we can use these kinds of hints. But thanks for the tip, this could be really useful!
- mst 5y ago
  Given Aurora talks about itself as a replacement for pg/mysql that provides compatible skims I'm honestly wondering whether you're dealing with the pg optimiser here in any meaningful way. (exactly how far along the scale from 'tweaked version' to 'basically just a skin on a completely new database' aurora is is ... opaque to me ... there's probably something out there that answers that but I haven't yet managed to find it) Edited to add: zorgmonkey downthread points out they have support for some extensions like pg_hint_plan - https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Maintenance.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide... - which suggests at least a decent amount of pg code but I'm still curious exactly where the changes start.

Justsignedup 5y ago

heh i had a similar style problem, i think it was more 1000x than 200x, but its because certain query structures the optimizer couldn't figure out, had to try like 4 different re-writes of the sql till the optimizer could understand it. I guess at this point I just see it as just part of my job to do this insane stuff, good to know that others see this as hard :)

hnov 5y ago

In this case denormalizing the data right into the runs table would likely give you better, but more importantly more predictable performance.

no-s 5y ago

It’s impressive how many developer misconceptions I see cropping up in these articles. The point of the query planner is to replace an army of programmers, not evade design faults. Using a server RDBMS it’s trivially easy to decouple schema and query evolution from application. Yet somehow developers always seem to back themselves into situations requiring extreme coupling. Using plan guides in the MSSQL environment helps somewhat in patchwork solutions, tho typically the deficiencies are usually related to obsolete statistics…(and that’s not even necessary). In Postgres you should ensure design separation so you can rewrite the query easily.

newlisp 5y ago
Using a server RDBMS it’s trivially easy to decouple schema and query evolution from application. Curious, how do you decouple query evolution from the DB layer of your application?
- dragonwriter 5y ago
  > Curious, how do you decouple query evolution from the DB layer of your application? Use application-specific views, and the evolution driven by DB optimization, etc., can happen in view definitions without the app DB layer being involved. It’s a traditional best practice anyway, going back decades to the time when it was expected an RDBMS would serve multiple apps that you needed to keep isolated from each others changes, but it also works to isolate concerns at different levels for a single-app stack. (Because of practical limits of views in some DBs, especially in the 1990s, and developer preferences for procedural code, a common enterprise alternative substitutes stored procs for views, to similar effect.)
  - newlisp 5y ago
    Oh yes views, they are not with no effort and become numerous and specific. But are extremely missed with a document store I'm working on write now ;)
  - no-s 5y ago
    hah, exactly. “application-specific views...Going back decades”, or “substitute Stored Procs for views”. It’s not really much extra overhead to use a schema+views. I ask developers to avoid creating views with “Select *”, always specify the columns desired. Further decoupling is possibly beneficial, e.g. CQRS is wonderful perspective for designing data models that may be more easily distributed or cacheable (by explicitly choosing to separate query schema from modification schema).

AaronFriel 5y ago

Regarding this: > Only a minuscule part of the Runs in the database are active at any given time. Most Stacks stay dormant most of the time. Whenever a user makes a commit, only the affected Stacks will execute Runs. There will obviously be Stacks that are constantly in use, but most won’t. Moreover, intensive users will usually use private Worker Pools. The public shared Worker Pool is predominantly utilized by smaller users, so that’s another reason for not seeing many Runs here. > Right now, we’re iterating over all existing Stacks attached to the public worker pool and getting relevant Runs for each. Is there an opportunity to make this query more robust with a bit of denormalization, if necessary, to indicate which runs and which stacks are active and a filtered index (index with a WHERE clause) on each to allow efficiently retrieving the list of active entries? Best practices for database optimization tell you "don't index on low cardinality columns" (columns with few unique values), but the real advice should be "ensure each valid key of an index has a low cardinality relative to the size of the data" (it's OK to have an index that selects 1% of your data!). That is, conventional wisdom says that indexing on a boolean is bad, because in the best case scenario 50% of your entries are behind each key, and in the worst case almost all entries are behind either "true" or "false". There are performance impacts from that and there's the risk the query optimizer tries to use the "bad" side of the index, which would be worse than a full table scan. But with the improved advice and insight that 0.01% of rows have value "true", you can create an index with a `WHERE active = true` clause, et voila: you have an index that finds you exactly what you want, without the cost and performance issues of maintaining the "false" side of the index.

cube2222 5y ago
Yep, you could indeed use a partial index with the two relevant conditions to solve this query well. We're using those all over the place as well.

deleted 5y ago

[deleted]

_xnmw 5y ago

Are you happy with Aurora Serverless so far? Is it really the "painless scaling" it's made out to be? I'm really hesitant to use anything other than self-hosted Postgres with full control, because I'm always afraid of running into situations like these and not being able do what I need to do, like have an offline analytical read replica. Also I imagine the cost is an order of magnitude more than a self-hosted instance.

cube2222 5y ago
Not really. But it's mostly because the support for other features is basically non-existant. It also seems like all development is happening on Aurora Serverless v2 (which I think is in preview state?). The scaling also isn't seamless, it breaks your connections if you're using prepared statements - we have it pinned to a specific number of capacity units. We'll probably move to plain Aurora at some point, as that supports all bells and whistles, but for now Aurora Serverless is good enough.

Mathnerd314 5y ago

Similar article that instead solved it by recording more statistics: https://build.affinity.co/how-we-used-postgres-extended-statistics-to-achieve-a-3000x-speedup-ea93d3dcdc61?gi=2c35fecd14f8 https://build.affinity.co/how-we-used-postgres-extended-stat...

unnouinceput 5y ago

Whenever I see "select count(star_sign)" I shake my head. The first optimization you learn as a newby dabbling in SQL is to always use "select count(some_fucking_id)" and not "star_sign". Who wrote this query should be fired, because it has 2 of this problem, one in a subquery!

cube2222 5y ago
That's harsh, could you explain why, please? As far as I understand (and I'm the author) `COUNT(*)` means "count the number of rows, regardless of the values". `COUNT(id)` means "count all non-NULL id's" which ends up being the same for non-nullable columns, but requires additional processing for nullable ones. Thus the first option should be able to optimize out more processing time in some situations and at worst be the same. This is confirmed by some quick googling, it's also confirmed by me running the query with a COUNT(*) and COUNT(id) - both run in the same amount of time, and it's also how it works in a SQL engine[0] I've implemented - COUNT(*) is able to optimize out the most underlying data loading. [0]:https://github.com/cube2222/octosql https://github.com/cube2222/octosql
- unnouinceput 5y ago
  Yeah, if you have the ID's as null. But why you would do such thing in the first place? Have the ID be "serial primary key" and you'll never have null values in there ever! Btw, that's the second optimization you learn. So you telling me that in your case, you have null values in your ID's? You should be fired trice then. Since you're hell bent on doing tests, do one with a dozen millions rows, just ID and a varchar as columns, and in one case have the ID have nulls, and in the other case be like I said. Then see the difference when you run "select count(ID) where the_varchar_column = 'Steve'" vs "select(star_sign) bla bla" and come back to me. Image would be appreciated. Then go rewrite your DB structure and the query when you realize the performance
  - option_greek 5y ago
    I'm not sure what value your harsh tone adds to your comment. Sharing experiences like this publicly benefits everyone. It obviously exposes blindspots in one's knowledge and it is expected that noone knows everything. That is the point of the. Comments functionality in HN. So that we can all share things that we know and absorb things we don't. I personally enjoyed this article and learnt a few things from it. I learnt a few things from your comment too but it would have been better without all the harsh words.

JoelJacobson 5y ago

Deduced schema based on the naming of tables/columns: CREATE TABLE accounts ( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY, max_public_parallelism integer NOT NULL, PRIMARY KEY (id) ); CREATE TABLE worker_pools ( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY, is_public boolean NOT NULL, PRIMARY KEY (id) ); CREATE TABLE stacks ( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY, worker_pool_id bigint NOT NULL, account_id bigint NOT NULL, PRIMARY KEY (id), FOREIGN KEY (worker_pool_id) REFERENCES worker_pools, FOREIGN KEY (account_id) REFERENCES accounts ); CREATE TABLE runs ( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY, stack_id bigint, worker_id bigint, created_at timestamptz NOT NULL DEFAULT now(), type integer NOT NULL, state integer NOT NULL, PRIMARY KEY (id), FOREIGN KEY (stack_id) REFERENCES stacks );

JoelJacobson 5y ago
cube222, does the schema above look more or less correct to you? Below are the query plans I got when running the query against empty tables in PostgreSQL 15dev (not Aurora Serverless), for comparison. Would be nice to populate the tables and add indexes, to make a real comparison. Would help a lot with some rough estimates on the number of rows in each table and count(distinct ...) of each column, etc? Unoptimized version: QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=82.14..82.16 rows=1 width=16) (actual time=0.009..0.012 rows=1 loops=1) -> Hash Join (cost=46.05..82.13 rows=1 width=8) (actual time=0.005..0.007 rows=0 loops=1) Hash Cond: (stacks.worker_pool_id = worker_pools.id) Join Filter: ((accounts.max_public_parallelism / 2) > (SubPlan 1)) -> Nested Loop (cost=0.30..36.38 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=1) -> Nested Loop (cost=0.15..36.18 rows=1 width=24) (actual time=0.004..0.005 rows=0 loops=1) -> Seq Scan on runs (cost=0.00..28.00 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1) Filter: ((worker_id IS NULL) AND (type = ANY ('{1,4}'::integer[])) AND (state = 1)) -> Index Scan using stacks_pkey on stacks (cost=0.15..8.17 rows=1 width=24) (never executed) Index Cond: (id = runs.stack_id) -> Index Scan using accounts_pkey on accounts (cost=0.15..0.20 rows=1 width=12) (never executed) Index Cond: (id = stacks.account_id) -> Hash (cost=32.00..32.00 rows=1100 width=8) (never executed) -> Seq Scan on worker_pools (cost=0.00..32.00 rows=1100 width=8) (never executed) Filter: is_public SubPlan 1 -> Aggregate (cost=66.89..66.90 rows=1 width=8) (never executed) -> Nested Loop (cost=33.72..66.89 rows=1 width=0) (never executed) -> Hash Join (cost=33.56..58.71 rows=1 width=8) (never executed) Hash Cond: (runs_other.stack_id = stacks_other.id) -> Seq Scan on runs runs_other (cost=0.00..22.00 rows=1194 width=8) (never executed) Filter: (worker_id IS NOT NULL) -> Hash (cost=33.55..33.55 rows=1 width=16) (never executed) -> Seq Scan on stacks stacks_other (cost=0.00..33.55 rows=1 width=16) (never executed) Filter: (((worker_pool_id IS NULL) OR (worker_pool_id = worker_pools.id)) AND (account_id = accounts.id)) -> Index Only Scan using accounts_pkey on accounts accounts_other (cost=0.15..8.17 rows=1 width=8) (never executed) Index Cond: (id = accounts.id) Heap Fetches: 0 Optimized version: QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=82.14..82.16 rows=1 width=16) (actual time=0.010..0.012 rows=1 loops=1) -> Hash Join (cost=46.05..82.13 rows=1 width=8) (actual time=0.006..0.007 rows=0 loops=1) Hash Cond: (stacks.worker_pool_id = worker_pools.id) Join Filter: ((accounts.max_public_parallelism / 2) > (SubPlan 2)) -> Nested Loop (cost=0.30..36.38 rows=1 width=28) (actual time=0.005..0.006 rows=0 loops=1) -> Nested Loop (cost=0.15..36.18 rows=1 width=24) (actual time=0.005..0.005 rows=0 loops=1) -> Seq Scan on runs (cost=0.00..28.00 rows=1 width=16) (actual time=0.004..0.005 rows=0 loops=1) Filter: ((worker_id IS NULL) AND (type = ANY ('{1,4}'::integer[])) AND (state = 1)) -> Index Scan using stacks_pkey on stacks (cost=0.15..8.17 rows=1 width=24) (never executed) Index Cond: (id = runs.stack_id) -> Index Scan using accounts_pkey on accounts (cost=0.15..0.20 rows=1 width=12) (never executed) Index Cond: (id = stacks.account_id) -> Hash (cost=32.00..32.00 rows=1100 width=8) (never executed) -> Seq Scan on worker_pools (cost=0.00..32.00 rows=1100 width=8) (never executed) Filter: is_public SubPlan 2 -> Aggregate (cost=9851.00..9851.01 rows=1 width=8) (never executed) -> Seq Scan on runs runs_other (cost=0.00..9850.00 rows=398 width=0) (never executed) Filter: ((worker_id IS NOT NULL) AND ((SubPlan 1) > 0)) SubPlan 1 -> Aggregate (cost=8.18..8.19 rows=1 width=8) (never executed) -> Index Scan using stacks_pkey on stacks stacks_1 (cost=0.15..8.18 rows=1 width=0) (never executed) Index Cond: (id = runs_other.stack_id) Filter: ((account_id = accounts.id) AND (worker_pool_id = worker_pools.id))