64 ms·
Making a Postgres query 1k times faster
- astralagent 2y agoGiven Post.ID is monotonically increasing and this is a cursor-based pagination query, can this WHERE clause be effectively rewritten as: WHERE Posts.CreateAt >= ?1 AND Posts.Id > ?2 (where ?1 and ?2 are the current cursor values)
- paulddraper 2y agoI'm gonna save you 15 minutes. The author filtered with: CreateAt > $1 OR (CreateAt = $1 AND Id > $2) That can't use one index for both conditions. Instead, make it faster and simpler: (CreateAt, Id) > ($1, $2) End of post.
- damidekronik 2y agoI think a nicer takeaway is what the author summarized with: 1. Always use BUFFERS when running an EXPLAIN. It gives some data that may be crucial for the investigation. 2. Always, always try to get an Index Cond (called Index range scan in MySQL) instead of a Filter. 3. Always, always, always assume PostgreSQL and MySQL will behave differently. Because they do.
- mewpmewp2 2y agoThat is a bit wrong and I'm pointing it out because your summary logically didn't make sense to me. So I did have to read the blog post after reading your comment. The query should be CreateAt > $1 OR (CreateAt = $1 (NOT $2 like in your sample) AND Id > $2) So the idea is here about paginating through posts that might be constantly changing so you can't use a simple offset, as that would give you duplications along the way. So you try to use CreateAt, but it could be possible that CreateAt is equal to another one so you fallback to ID. But here I stopped reading the blog post, because I now think why not use Id in the first place since it also seems to be auto increment since otherwise you couldn't really rely on it to be a fallback like this? I don't have time to investigate it further, but tldr; that still left me confused - why not use ID in the first place.
- selecsosi 2y agoThey could be using something like application generated time sortable UUID7s or some other sortable key https://uuid7.com/ https://uuid7.com/ [edit] CreatedAt timestamp could be something from the client when the post is submitted or tagged from the ingest server and not when they actually are processed and hit the database
- mewpmewp2 2y agoFor sure, but still why not use that in the first place? And I agree I shouldn't have said "auto increment".
- selecsosi 2y agoIt depends on the resolution of the timestamp. With 1000s of posts a second coming in, the fallback would be that it is at the same "timestamp" and then we fallback to a greater identifier. My guess is that the resolution of the timestamp allows for more efficient postgres index usage then the id index which my guess is larger and a string so not as efficiently searchable as the underlying identifier (my guess is time based index offer faster search performance or lower overhead than a string based search index which doesn't understand it is representing encoded time data)
- mewpmewp2 2y agoI'm only speculating here, but it doesn't seem right to me at all to end up with a solution like that. I don't think there would be a reason where this ID would perform so bad as an index that it would be worth what I would consider odd reasoning and tech debt if I'm understanding things correctly. And clearly the query is tech debt because now it did cause such an issue. But also I think current solution seems like tech debt to me, having high odds of causing issues in the future, if the DB driver was to be changed or something about the way DB resolves indexes was to change, etc. And with fallback they would end up reusing that index anyway.
- 2y ago
- OJFord 2y agoAgree it's well-known, in a slightly snobbish sort of way, but it could do with being more widely known for sure; it is a bit of a gotcha, and IMO TFA is quite a nice tutorial on how they went about it, investigating the issue and determining the problem etc., perhaps especially if the answer as you wrote isn't already somewhat familiar, the explain analyze -err-explainer is quite nice. Not reference material, but as an intro, hey this is how you might be able to debug a similar problem you encounter.
- aidos 2y agoThanks. Too late though, I already read it. The article felt like it was fumbling around when the initial explain pointed right at the issue. I didn’t know this specific trick, so I did learn something I guess.
- tomrod 2y agoIs that logically the same filter syntax? I'm not familiar with the second construct. If creatat is equal to $1, does it compare the second argument?
- paulddraper 2y agoYes
- xxs 2y agoThat looks like a trivial optimizaiton / transformation. I am still quite puzzled how much work has gone into compilers, static code analysis, guided compilation (naturally comes w/ JITs, too). Yet, database optimizers of pretty much any database look rudimentary and unreliable. Sometimes I think they got stuck in the '90s and that's it.
- Bjartr 2y agoSame with debugging and error messages. At least it feels that way to me.
- olliej 2y ago[edit: holy downvotes folk, the referenced 'olliej' user is me - I'm not shitting on others] Based on the last time I did anything involving databases, I think the easy way to do this is "find a query written by someone like olliej, and then fix the clear and obviously stupid choices" :D
- mewpmewp2 2y agoTo your edit: Sure, but if you are going to be self deprecating, at least provide some example content of the hilariously bad queries you have written and how you have been chastised for them.
- MuffinFlavored 2y ago> WHERE (Posts.CreateAt, Posts.Id) > (?1, ?2) Did not know you could do "tuple" filtering like this
- hansonkd 2y agoOR Queries are performance killers because they often force a full table scan. Another alternative to the Or is actually a UNION. A UNION allows you to use two separate indexes and can speed up queries.
- Tostino 2y agoIf at all possible, use a union all rather than a plain union to avoid an extra sort / unique node. I've used that OR > UNION ALL trick a number of times to vastly improve performance on specific queries. It's crazy how much of an effect it can have. I wish Postgres would implement a planner optimization to automatically run queries with an OR more efficiently (e.g. use the same plan as with a UNION/ALL where possible).
- hinkley 2y agoWhat do you suppose prevents the query planned to do the same thing?
- Tostino 2y agoI remembered reading a thread on the hackers mailing list about this starting probably 6 or 7 years ago... Nothing ever came of it if I remember right. Found it: https://www.postgresql.org/message-id/flat/7f70bd5a-5d16-e05c-f0b4-2fdfc8873489%40BlueTreble.com https://www.postgresql.org/message-id/flat/7f70bd5a-5d16-e05... I just did some more digging, and it looks like there has been some more recent discussion in a different thread about the same topic though: https://www.postgresql.org/message-id/flat/567ED6CA.2040504%40sigaev.ru https://www.postgresql.org/message-id/flat/567ED6CA.2040504%...
- boomskats 2y agoI have a vague memory of this exact optimisation being one of the things Hibernate did automagically, about 10 years ago. I remember doing a fair bit of reading to figure out how & why it was so much faster.
- hansonkd 2y ago
- brunooliv 2y agoGreat post thanks for sharing this “from the trenches” it’s always a joy to read these deep dives! Thanks
- mewpmewp2 2y agoMy question after a brief look - why not just use ID instead of CreateAt?
- deleted 2y ago[deleted]
- deleted 2y ago[deleted]
- thatwasunusual 2y agoThat was my initial thought as well. I'm hoping the original author reads this and can explain, because surely there must be a reason...?
- aleksiy123 2y agoI think because ID is random and not monotonically increasing. From article one of them is: 'tpomh9yu1tffmdp6dopobwuc9h' But the query is trying to page through the most recent posts first. So a newer post doesn't necessarily have a higher ID or lower ID. The ID is just there to have a stable tie break between the same timestamps.
- Delmololo 2y agoIn this case they should switch to uuidv7 or similar to have this guarantee
- aleksiy123 2y agoSwitching IDs is going to be a breaking change across their whole product and customer base. Thats a way bigger undertaking and decision then just optimizing a single query. Like killing an ant with a Tsar Bomb.
- throwaway28453 2y ago
- Someone 2y agoFTA: “Why the query planner doesn’t automatically convert a condition like x > a OR (x == a AND y > b) to (x, y) > (a, b) is something I still don’t understand to this day, though.” There’s a difference between the two. https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-EXPRESS-EVAL https://www.postgresql.org/docs/current/sql-expressions.html...: “The order of evaluation of subexpressions is not defined. In particular, the inputs of an operator or function are not necessarily evaluated left-to-right or in any other fixed order. Furthermore, if the result of an expression can be determined by evaluating only some parts of it, then other subexpressions might not be evaluated at all. For instance, if one wrote: SELECT true OR somefunc(); then somefunc() would (probably) not be called at all. The same would be the case if one wrote: SELECT somefunc() OR true; Note that this is not the same as the left-to-right “short-circuiting” of Boolean operators that is found in some programming languages.” https://www.postgresql.org/docs/current/functions-comparisons.html#ROW-WISE-COMPARISON https://www.postgresql.org/docs/current/functions-comparison...: “For the <, <=, > and >= cases, the row elements are compared left-to-right, stopping as soon as an unequal or null pair of elements is found.” So, this is using something akin to short-circuiting. I think that can mean the two give different results in the presence of null values. ⇒ I would guess the optimizer isn’t smart enough to detect when the second, stricter query will be equivalent and faster.
- deleted 2y ago[deleted]
- mannyv 2y agoTL;DR: Step 1: use the index Step 2: see step 1
- durkie 2y agoNot at all surprised that Laurenz Albe played a key role in figuring this out -- I've learned so much about postgres from his Stack Overflow answers over the years.
- estebarb 2y agoMaybe I'm sleepy, but what is doing elasticsearch in that post?
- lmz 2y agoconsuming the data returned by the query
- sbstp 2y agoI generate queries like this in a paging library I wrote at work, I did not use the tuple comparison because sometimes the sorting order is different between fields, each field in the tuple comparison would need a different operator, such as WHERE Posts.CreateAt > ?1 OR (Posts.CreateAt = ?1 AND Posts.Id < ?2) ORDER BY Posts.CreateAt ASC, Posts.Id DESC I now wonder if it might be possible to use a negation operator to invert the sorting order, like: (Posts.Id, -Posts.CreateAt) < (?1, ?2) ORDER BY Posts.CreateAt ASC, Posts.ID DESC and keep the performance improvement.
- Delmololo 2y agoThis post is from a few days back. Does mm not have the stats table active and check it sometimes for this kid of issues?
- DeathArrow 2y agoHunting and solving bugs is good but there's a better alternative: having pipelines that run performance regression tests before commits are merged in master branch.
- DeathArrow 2y agoI wonder if using an ORM wouldn't actually be better than writing queries.
- Guillaume86 2y agoDon't have a postgres under the hand, would adding a AND condition WHERE CreateAt >= $1 AND (CreateAt > $1 OR (CreateAt = $1 AND Id > $2)) workaround the query planner limitations as well?
- merb 2y agoBut > CreateAt > ?1 OR (CreateAt = ?1 AND Id > ?2) Is not the same as: > CreateAt, Id) > (?1, ?2) Because it is like that: > createdAt > ?1 OR (createdAt <= ?1 and Id > ?2) Or am I wrong?!