Tricking PostgreSQL into using an insane, but faster, query plan
spacelift.io
spacelift.io
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-e...
(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... - which suggests at least a decent amount of pg code but I'm still curious exactly where the changes start.
But it -does- still leave the question of how well the storage layer reports table statistics and how that interacts with the planner.
If I sound suspicious, it's because I ran into a situation recently where a SELECT inside a transaction on mysql Aurora somehow (as far as I can tell, I was helping out at a couple removes so if you're reading this and it's relevant to you please don't believe me without testing yourself) took a whole table lock in a situation where I am pretty damn sure that for all its 'fun' actual InnoDB would've locked a page at most.
So ops goes.
Also having the optimizer decide to do something bad off hours is not a good situation again hints or plan locking would help here.
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.
The Postgres query planner was, quite frankly, the enemy. By the time I left we were at the point that we considered it a business risk and were looking at alternatives. If you need queries to run in a predictable amount of time — forget fast, simply predictable — then Postgres is quite simply not fit for purpose.
A lot of times I see in operational DB hot data gets mixed with warm and cold data, and your hot path query with ton of JOINs/subqueries will get rekt.
Proper redesign and rearchitecture helps to provide large buffer against these problems.
Also not everything should be inside SQL server, if you run large query every 5 seconds or something - probably consider using in-memory cache or denormalized model
Besides which, if you need to re-architect and denormalize by moving records in and out of different tables just so you can run what is essentially a monthly report in a predictable (not fast! just predictable) amount of time -- well then, why bother using Postgres in the first place, you might as well bite the bullet and go with something NoSQL at that point, because at least when you read from a given Cassandra partition you know all the results will be right next to each other, 100%; why leave it to chance? you've been burned by Postgres before
And Postgres, again, is materially deficient in its query performance predictability, and its developers are insistent that they have no desire to allow the approaches that would mitigate it. If this matters to your application, then the prudent developer will drop Postgres and use a real database.
I have been working with it since way back in 6.5 and it has made many obviously wrong decisions in the optimizer over the years, its has gotten better but is by no means perfect, good thing is it has hints unlike PG!
Just splitting hairs, again most of the time developers fail to notice the significant data distribution drift, and dont rearchitect their data models then blame the engine for developers omissions and invalid assumptions
Postgres generally is great -- so many great features and optimizations and more added all the time. But its query optimizer still messes up, often. It's absolutely not the engineering marvel some would have you believe.
Don't articles and anecdotes also come up again and again of developers feeding bad happens to the database and cratering their performance?
And that wiki page... Very explicitly isn't a blanket ban? They straight up say that they're willing to consider the idea if somebody wants to say how to do it without the pitfalls of other systems. The only thing they say they're going to ignore outright is people thinking they should have a feature because other databases have it (which seems fair).
Literally NO ONE wants query hinting in Postgres to check some kind of feature box because other databases have it.
We know we want it because…other databases have it, and it's INCREDIBLY USEFUL.
> They straight up say that they're willing to consider the idea if somebody wants to say how to do it without the pitfalls of other systems.
Pitfalls my ass. We want exactly the functionality that is already present in other systems, pitfalls and all. That's just an excuse to do nothing, Apple-style, "because we know better than our own users" while trying to appear reasonable.
It's akin to not adopting SQL until you can do so "while avoiding the pitfalls of SQL." Just utter bullshit.
Are you offering to help with the maintenance or development associated with it? Or are you just demanding features while calling the people that do help with that stuff liars?
Maybe they do know better? Or maybe they know about other hassles that will come with it that you can't fathom. They've been developing and maintaining one of the best open source projects on the planet for nearly 3 decades. Maybe with all that experience, its not their opinion that is BS?
Also given Amazon AWS support it for Aurora, I feel like it's not -that- hobby-ish.
If people use it and can provide evidence that it helps more than it hurts, that might be convincing.
Insulting the core developers for not yet being convinced seems rather less likely to help.
Oh, didn't know that :)
About the rest: sure, I can agree as well about the indirect adverse effect of having them available (e.g. easy to misuse them as I saw in some apps using Oracle DBs), and it's for sure wrong to insult somebody because of this.
Still, personally, I think that the pros would outweight the cons of having that embedded in the app.
On one hand I remember some nights spent in the past trying to make some SQL work, hints were always at least a good temporary workaround.
On the other hand there will always be some SQL which confuse the optimizer (or more special cases about a lot of data changing distribution of values, etc..) and hints would be the only way to cover these cases.
Maybe an interesting question is on which level should hints act? I know mainly only Oracle & MariaDB, therefore I know hints of the type "use that index"/"query tables in this order"/"join these tables with this join type"/etc..., which are probably low-level hints. Maybe already just higher-level hints of the type "most important selectivity criteria comes from inline-view X"/"I want just the first row of the result"/"take into account only plans which select data by using indexes"/etc... would be as well interesting, not sure, just dumping here my thoughts.
Or maybe it's just typical developer hubris of the kind that hits everyone at some point.
There's a difference between "have a feature just because other databases have it" and "this is a very useful feature that would have its own PostgreSQL-isms and also we got this idea because other databases have it". The query planner isn't infallible, so being able to hint queries to not accidentally use a temporary table that just can't fit in ram isn't just copying a feature "just because everyone else has it".
That is basically a blanket ban. Saying you won't implement a widely-implemented feature unless someone comes up with a whole new theory about how to do it better is saying you simply won't implement it. Other databases do well enough with hints, and they do help with some problems.
I'd love to have a way to lock a plan in a temporary "emergency measure" fashion. But of course it's hard/impossible to design this without letting people abuse it and pretend it's just a hint system.
Could this be achieved by extending prepared statements? [1] The dirty option would be to introduce a new keyword like PREPAREFIXED.
The first time such a statement is executed, the execution plan could be stored and then retrieved on subsequent queries. There would be no hints and the changes in code should be minimal.
Once a query runs successfully, the users can be sure that the execution plan won't change.
>But of course it's hard/impossible to design this without letting people abuse it
Is this more important than having predictable execution times?
[1] https://www.postgresql.org/docs/current/sql-prepare.html
To me: no. To the Postgres team: apparently :)
I found the cons section somewhat disappointing given how much respect I have for Postgresql maintainers in general.
Most of the "problems" are essentially manifestations of dysfunction within users or PostgreSQL development itself. People won't report optimizer bugs if they can fix them themselves, etc. (far more likely they won't report the bugs if they have no way to prove their alternative query plan actually performs better, eg: by adding a hint).
Many are just assertions which I highly doubt would hold up if validated ("most of the time the optimizer is actually right", "hints in queries require massive refactoring" ...).
They don't need to run an alternative plan, they just need to report that the main plan did far more work than needed.
In the real world, when users have a problem in their mission-critical system, they ask for a solution, not just give up on the mission.
> Many are just assertions which I highly doubt would hold up if validated ("most of the time the optimizer is actually right", "hints in queries require massive refactoring" ...).
Their 20 years of experience beats your idle speculation.
Perhaps.
But it comes across to me on that page as arrogance and dismissiveness of user needs. Hence why I find it disappointing, even if it is mainly in how it is expressed.
And how do they know that the chosen plan is not already optimal? How many reports do you think you'd get if every time someone encountered a "slow" query they reported it? And what fraction of those would be finally found to be caused by a bad optimizer as opposed to the user's fault like a missing index, outdated statistics, ... 1%? 0.1%? See stack overflow for a sample of that ratio. Be careful what you wish for.
If hints existed performance reports could be "default plan of x is slow, I know because when I use hints w,z then it's fast".
What should they do in the meantime while they're waiting for the pg project to acknowledge the problem, someone to propose a solution, and someone to implement a fix, just sit on their hands and twiddle their thumbs while their database server spews smoke? Hints are an escape hatch that empowers users to take over a when the optimizer veers off course. Not having any way to control the plan is like a tesla autopilot that doesn't allow the driver to take control over the car in an emergency.
Also, distrusting users to report performance issues is a weird attitude and gives a bad taste. Maybe it's true, or maybe you need to make it easier.
It's almost self-evident that this is the case, because if they had, they'd have implemented some more fine-grained way of overriding the query optimizer when it inevitably fails at times.
I have one shameful query where, unable to convince it to execute a subquery that is essentially a constant for most of the rows outside of the hot loop of the main index scan, I pulled it out into a temp table and had the main query select from the temp table instead. Even creating a temp table and indexing it was faster than the plan the Postgres query planner absolutely insisted on. Things like CTEs etc made no difference, it would still come up with the same dumb plan every way I expressed the query.
The worst thing is not even being able to debug or understand what is going on because you can't influence the query plan to try alternative hypotheses out easily.
Hints are a somewhat invasive feature that are hard to tweak once they are integrated into applications. I don't think the half dozen people that are in the best position to consider its evolution have found it the use of their time they wish to expend.
There are times when I already know how I want the query to execute, and I have to iteratively fiddle with the query to indirectly trick the planner into doing that. There's just no excuse for that arrangement. Even if I've made a mistake and my plan couldn't work efficiently – at least let me execute it inefficiently so I can discover and debug that.
Having used Linq a lot I would actually prefer more of that kind of chained statement approach that is more hands on without having to explicitly loop.
I've heart the rebuttal to this idea before, that even minor DB schema/configuration changes that should be "invisible" to the application (e.g. an index being created — or even refreshed — on the table) would result in the resulting bytecode being different; and so you'd never be able to ship such bytecode as a static artifact in your client.
But: who said anything about a static artifact? I'm more imagining a workflow like this:
1. user submits a query with leading command "PLAN" (e.g. "PLAN SELECT ...")
2. Postgres returns planned bytecode (as a regular single-column bytea rowset)
3. if desired, the user modifies the received bytecode arbitrarily (leaving alone anything the client doesn't understand — much like how optimization passes leave alone generated intrinsics in LLVM IR)
4. user later submits a low-level query-execution command over the binary wire protocol, supplying the (potentially modified) bytecode
5. if the bytecode's assumptions are still valid, the query executes. Otherwise, the query gives a "must re-plan" error. The client is expected to hold onto the original SQL and re-supply it in this case.
In other words, do it similarly to how Redis's EVAL + EVALSHA works; but instead of opaque handles (SHA hashes), the client receives a white-box internal representation.
Or: do it similarly to how compiling a shader on a game-console with unikernel games + unified RAM/VRAM works. The driver's shader compiler spits you back a buffer full of some GPU object code — which, running in kernel-mode, you're free to modify before running.
You -can- use a server side prepared statement to force it to -ahem- plan ahead but that's -usually- not actually worth it.
And using prepared statements only works on the same connection so of limited use.
Luckily PostgreSQL's optimizer is very primitive and so planning doesn't take too much time, as it gets more advanced the lack of plan reuse will become more of an issue. Its already an issue with the LLVM JIT compilation time.
The server is supposed to take the logical intent specified by the queries and do the work to mapping that into concrete retrievals in a stable and performant way, so a query explicitly telling the server to do something in a specific way breaks the model. The more specific, the more broken.
DDL changes for instance ideally would not have to tell the server how to structure things on disk. The server should know how to do it efficiently.
That said, real life intrudes and sometimes (but not usually) it’s important to do this for stability or performance reasons. The more magic involved, the more unpredictable a system can be, and the closer a system runs to redline, the more chaos/downtime that can cause.
And now you've invented Firebird, or even 1980's Interbase. ;)
We also write queries knowing it will use a specific index, or we will create an index because of a specific query. And then we have to have a scheduled task to periodically recalculate statistics just so the DB server doesn't get silly ideas.
Of course it could be abused, but I'm in favor of having ways of letting programmers tell computers exactly what to do. Sometimes we really do know best.
> Also having the optimizer decide to do something bad off hours is not a good situation again hints or plan locking would help here.
Can also happen when you force a particular query plan, only for it to turn to treacle when some assumption you made about the data suddenly breaks.
As with most things there's no free lunch.
The system randomly deciding to drive itself off a cliff for no reason, with no known way to stop it next time is quite concerning.
But if the query planner decides to screw up your query plan it happens instantaneously and there's no possibility of rollback - only emergency deploying code fixes to try to tweak the query. In most OLTP use cases, you almost always know exactly how a query is supposed to execute, so always using index hints is totally reasonable to prevent bad query plans.
Basically, you breaking your own query with bad hints usually breaks things a lot less and at better times than the query planner doing it, and is usually easier to fix too.
The idea obviously needs work and has probably already been suggested and dismissed, somewhere, but I thought I might throw it out there, especially with modern computers having so many cores.
On the other hand, in general there are often too many postential combinations of query plans to try out (hundreds even for a relatively simple SQL) and trying them all out would need hours/days/etc... . The "good plan" might be something that a machine might categorize as "very unlikely to work" so it might end up being the one tested automatically at the very end.
Normal hints would still be a lot easier to handle in the code and for the user.
> 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.
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.)
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).
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.
The article describes a fun debugging and optimization session I’ve had recently. Happy to answer any questions!
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.
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.
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)
[0] https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
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.
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.
> WITH tmp AS MATERIALIZED
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
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
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!
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 */
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 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
);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
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))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.
> 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.
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 :)
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
In this case we're using the Datadog Agent with its custom_queries directive to automatically create metrics based on periodically evaluated SQL queries.
As the shape of your data changes over time, your hard coded algorithm choice is not guaranteed to be the best one.
Or of course, you can just use temp tables.
https://docs.snowflake.com/en/sql-reference/functions/result...
Sometimes you can follow this pattern and never actually pull data into your application, which is especially true when you're doing ETLs (table --> table).
Are you talking about CTEs? And if so, are you suggesting that each "temp table" in the CTE is calculated/materialized a step at a time? If so, I see the query optimizer tear these barriers down and fusing all of the SQL together on a daily basis.
https://www.postgresql.org/docs/14/queries-with.html#id-1.5....
> However, if a WITH query is non-recursive and side-effect-free (that is, it is a SELECT containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels. By default, this happens if the parent query references the WITH query just once, but not if it references the WITH query more than once. You can override that decision by specifying MATERIALIZED to force separate calculation of the WITH query, or by specifying NOT MATERIALIZED to force it to be merged into the parent query. The latter choice risks duplicate computation of the WITH query, but it can still give a net savings if each usage of the WITH query needs only a small part of the WITH query's full output.
Before PG12 this used to be the only way CTEs worked. Now most CTEs don't need to be materialized, but you can force it and reestablish the optimization barrier.
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.
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
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.
It would be great if you'd explain why what I've written is not the case or provided some citation for that.
nice tool, thanks for making this available.
First result in Google confirms that at least for Postgres: https://www.citusdata.com/blog/2016/10/12/count-performance/
Another one from stackoverflow: https://stackoverflow.com/questions/2710621/count-vs-count1-... with a citation:
> Don't be fooled by those advising that when using * in COUNT, it fetches entire row from your table, saying that * is slow. The * on SELECT COUNT(*) and SELECT * has no bearing to each other, they are entirely different thing, they just share a common token, i.e. *.