6 ms·
A PostgreSQL planner semi-join gotcha with CTE, LIMIT, and RETURNING
- wordofx 1y agoWhy was the author not just doing returning * No need for the CTE to select everything…
- tomnipotent 1y agoCould be a generational thing. There was a time not every query planner would properly eliminate columns based on the final projection and any intermediary copies required more I/O if you included everything. Even now I still find myself following "best practices" for things that haven't been relevant in a decade.
- abhisek 1y agoInteresting to think about how to guard against these cases where query optimisation leads to unexpected results. I mean this could lead to serious bugs. What can be a way to detect these using linters or in CI before they hit the production.
- magicalhippo 1y agoOn a somewhat related note... We've moved to MSSQL due to several reasons including customer demand. We're experiencing the MSSQL query planner occasionally generating terrible plans for core queries which then gets cached, leading to support calls. Workaround for now has been to have our query geneator append a fixed-value column which and have it change the value every 10 minutes, as a cache defeat. Still, surprised the engine doesn't figure this out itself, like try regenerating plans frequently if they contain non-trivial table scans say. Or just expire cache entries every 15 minutes or so, so a bad plan doesn't stick around for too long.
- baq 1y agoIt’s one of RDBMS peculiarities that you just need to learn about. Any half decent MSSQL course or book will tell you about the plan cache and the occasional gotcha exactly like yours. Time wasted debugging this issue by the unaware is probably in kilo man years by now. …but MSSQL is still a fantastic database, if you can afford it. Postgres and mysql come with their own set of gotchas, some of which need an actually decent book to be explained. (Note all the RDBMS manuals are decent books and everyone without exception should read at least the TOC of the db they’re using, which IME is still a rarity.)
- magicalhippo 1y agoMy point was more that I feel it's a discrepancy that the DB tries to be very clever, but seemingly doesn't self-evaluate its decisions enough to determine when it wasn't. Why should I have to guard against poor optimization decisions when the whole point is to just let the DB figure out the best way?
- baq 1y agoBecause the point isn't that the DB should figure everything out, as it is a baseless claim that it's a 'poor optimization decision'. The point is to allow you to meet your requirements within your constraints. You're trading some of your constraints for different ones (here - you don't want to implement your own database, so you're using one that's already built with its own requirements and constraints). A database is a database, not a holy grail. I haven't used MSSQL for some time now, but I'm sure there are tools which do exactly what you say you want, i.e. continuously monitor the DBMS and suggest actions or even act itself. This is usually done by a team of DBAs, though.
- rotis 1y agoTo add to sibling comment. This sounds like a parameter sniffing. Read it up. It should help you understand the problem. It is a known issue with SQL Server. Or rather a tradeoff. You don't want to compile a new execution plan for every query. This would eat up CPU by itself.
- dathinab 1y agoin general end to end tests/whole system tests which mock/stub as little as possible and are focused on testing whole use-case flows is a good starting point (sadly in many teams also a constant uphill battle from convincing PM over allocate time to bootstrap it to making CI harder due to basically having to bootstrap a whole testing system including db etc. to educating devs to write such tests properly, instead of writing unit tests at a end to end test level (which rarely ends well). Optimal you then combine this with automatic collection and analysis of log traced to detect performance regression (which is hard but just because of the involved tooling but now it needs a CI env with reliable perf. characteristics e.g. not GitHub hosted GitHub action runners). Through in this case the problem is a trap in the behavior of `IN (...)` sub queries which probably is not specific to WITH clause and might be as deeply rooted as the SQL standard but a you have to pay for being able to read it I'm not sure.
- swid 1y agoThis is interesting - I don’t expect undefined behavior like this. Should the query delete more than one row or not? The blog post doesn’t really give answers - it isn’t obvious to me that the second query can’t be executed in the exact same way. The even cop to this fact: > This structure appears less ambiguous to the planner and typically encourages it to evaluate the subquery first. So then - their bug still exists maybe? I have thoughts - probably we do expect it never should delete multiple rows, but after considering the query plan, I can see it being ambiguous. But I would have expected Postgres to use only a single interpretation.
- dankebitte 1y agoIt's not undefined behavior, it's the fact that the uncorrelated subquery within the CTE doesn't specify an ordering, therefore it cannot be implicitly materialized / evaluated once. Postgres documentation is clear here [1]: > If sorting is not chosen, the rows will be returned in an unspecified order. The original query from TFA could've instead just had the uncorrelated subquery moved into a materialized CTE. [1] https://www.postgresql.org/docs/current/queries-order.html https://www.postgresql.org/docs/current/queries-order.html
- swid 1y agoI see what you saying, but it is very subtle, wouldn’t you agree? Under a different plan, the inner query would only be evaluated once, it is hard to mentally parse how it will first find the rows with the group id and then pass into the sub query. And still I am not sure how using a CTE or not in the manner in the post is supposed to avoid this issue, so now I’m a bit skeptical it does. I see how a sort would. I hope if the sub query was its own CTE, the limit would be applied correctly, but am no longer sure… before this post I wouldn’t have questioned it. Edit to say - now I see you need to explicitly use AS MATERIALIZED if you bump the subquery to a CTE. Someone should really write a better blog post on this… it raises an interesting case but fails to explain it… they probably have not even solved it for themselves.
- deleted 1y ago[deleted]
- Horffupolde 1y agoIN is not recommended. Use = ANY instead.
- codesnik 1y agoaren't they the same in Postgres?
- frollogaston 1y agoYes, according to the manual, IN is equivalent to = ANY() https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-ANY-SOME https://www.postgresql.org/docs/current/functions-subquery.h... I had to check because for some reason, I always thought =ANY was somehow better than IN.
- masklinn 1y agoIt is, but in a pretty minor way: any can be used with no items, IN can not and will error.
- tczMUFlmoNk 1y agoANY can be used with arrays, particularly query parameters: `id = ANY($1::int[])`, but not `id IN $1::int[]`.
- frollogaston 1y agoOh that must be why I got into this habit.
- Sesse__ 1y agoIN is fine. The biggest problem comes with NOT IN, which has NULL semantics that makes life difficult for the planner. It is consistent but rarely what the user wants. NOT EXISTS is typically better there.
- simonw 1y agoDELETE FROM task_queue WHERE id = ( -- Use '=' for a single expected ID SELECT id FROM task_queue WHERE queue_group_id = 15 LIMIT 1 FOR UPDATE SKIP LOCKED ) RETURNING item_id; I don't understand where that item_id value comes from, since that's not a column that's mentioned anywhere else in the query. I guess it must be an unmentioned column on that task_queue table?
- dragonwriter 1y agoIn DELETE ... RETURNING, RETURNING works like SELECT in a read query, so, yes, item_id is a column in task_queue and the result set is the item_id value of the row deleted.
- eknkc 1y agoI don’t know about “gotcha”. This sounds like a full blown planner bug to me.
- swid 1y agoI think arguably there is no bug here, but the blog doesn't do a good job of explaining the issue and their fix may work because they remove the CTE and switch to `=`, but they prove they don't understand why the difference when they suggest "using id IN (...) might still be necessary"; if you do that, then the problem will return. There are two factors here. The subquery SELECT id FROM task_queue WHERE queue_group_id = 15 FOR UPDATE SKIP LOCKED LIMIT 1 can return different ids each time you run it. If it was ordered, then it would always return the same id and if postgres optimized in a way that it runs more than once it would just get the same result each time anyway. Otherwise, you need to force postgres to evaluate your subquery exactly once by materializing it. There are different ways this might be accomplished - the blog post does this incidentally by using `=`. But it is not the only way to tell postgres that. For instance, like this. But it is fragile - without AS MATERIALIZED, it could be run more than once. WITH candidate AS MATERIALIZED ( SELECT id FROM task_queue WHERE queue_group_id = 15 FOR UPDATE SKIP LOCKED LIMIT 1 ) DELETE FROM task_queue t USING candidate c WHERE t.id = c.id RETURNING t.item_id;
- shayonj 1y agoThat's correct, if you want to keep using CTE, the gurantee can be achieved via something like this WITH deleted_tasks AS MATERIALIZED ( SELECT id FROM task_queue WHERE queue_group_id = 5 AND status = 'pending' LIMIT 1 FOR UPDATE SKIP LOCKED ) DELETE FROM task_queue WHERE id IN (SELECT id FROM deleted_tasks) RETURNING item_id, id;
- tucnak 1y agoDELETE in SKIP LOCKED scenario is recipe for failure. God/Postgres gave you transactions for atomicity... only UPDATE w/ SKIP LOCKED, holy brother in Christ, and only DELETE completed normally.
- clhodapp 1y agoHmm, it seems like the subquery is getting re-run on every single row of the task queue table, which seems like a performance issue in addition to correctness. Personally, if I were going to put any part of that query in a CTE, it would have been (just) the select, as I generally prefer CTE's to subqueries. I'm really not sure what motivated the author to create the CTE in the first place, as the final select seems to be essentially the identity function.
- McGlockenshire 1y ago> it seems like the subquery is getting re-run on every single row of the task queue table Y'all might find the term "correlated subquery" helpful here. When I want them, I always get them. Sometimes I get them when I don't want them. Sometimes it's even my fault, not the result of the query planner looking at me sideways.
- Normal_gaussian 1y ago"I'm really not sure what motivated the author to create the CTE in the first place, as the final select seems to be essentially the identity function." We're likely seeing a simplified example. All queries should be able to be embedded in a CTE without integrity loss.
- zokier 1y agoI think CTE is red herring here, the problem is in the sub-query regardless if it's in CTE or not
- Normal_gaussian 1y agoYes, it is a red herring for sure; using a limit without a sort in a sub-query is always an issue.
- impulsivepuppet 1y agoSince I don't often write raw SQL, I can only assume the author named their CTE `deleted_tasks` to elucidate that the query might delete multiple items. Otherwise, it makes little sense, for they intended to "pop" a single row, and yet their aptly named `deleted_tasks` ended up removing more than one! The query reads to me like a conceptual mish-mash. Without understanding what the innermost `SELECT` was meant to accomplish, I'd naturally interpret the `WHERE id IN (...)` as operating on a set. But the most sacrilegious aspect is the inclusion of `FOR UPDATE SKIP LOCKED`. It assumes a very specific execution order that the query syntax doesn't actually enforce. Am I right to think that not avoiding lock contention, i.e. omitting `SKIP LOCKED` would have actually produced the intended result?
- phoronixrly 1y agoDELETE with an overly-broad operator in the WHERE clause and no explicit limit: check, non-trivial subquery in the WEHERE: check. This should not have passed code review, let alone have been caught in production. I will give OP the benefit of the doubt and say that automated testing did not catch this because the optimisations depend on table statistics, and not because it was not appropriately covered.
- i_have_to_speak 1y agoThis really has nothing to do with CTEs. The SQL standard does not specify how many times a subquery must or can be run. In this case, Postgres is free to run the uncorrelated subquery many times. Unfortunately, the subquery result is not idempotent. The real solution is not to rely on the optimizer for the correctness of your query. The query should be logically correct, irrespective of the query plan, right? The correct solution would be to ensure that the subquery runs only once, using a materialized cte or temp table. Do not base your "solution" on the discovery that for this particular version of Postgres, the planner runs this type of subquery once for the '=' operator and multiple times for 'IN'.
- Diggsey 1y agoBased on my understanding, your comment is the most accurate in this thread. However, I think it's telling that almost every single comment here has a different understanding of how PostgreSQL works, and nobody can link to any documentation that conclusively addresses this case. I think that indicates that this is a failing if not of implementation, then of documentation. For example: another comment says that `=` forces PostgreSQL to evaluate the subquery exactly once. I couldn't find any justification for that statement. Is it guaranteed? I don't know. Another comment says "if only the subquery was ordered then the result would be consistent", but AFAIK, this is not true in the presence of "SKIP LOCKED". I think the problem stems from the fact that the SQL standard is written from an academic point of view: the ordering of execution of subqueries is not defined because it is irrelevant within the relational algebra, so optimizations which change the execution order are just that: optimizations, with no observable side effects. Real database systems do not actually have the nice algebraic properties of the relational algebra... The same subquery may give different results over multiple executions, etc. Given this, it's important that the database system give sufficient and sufficiently clear guarantees such that they can still be used correctly, and so that the optimizations implemented by the system can be justified.
- swid 1y agoThe comment about = is mine and I thought about rewriting that part of the comment after the fact. It is soooo hard to explain these things without writing a book. The reason = mean it runs once is because the outer query will only run once, and in this case that query, when using =, can only delete based on a single id. But if that outer query was subquery in a context where it could be run more than once, you are back to where you started. Hence me saying their fix was sort of incidental.