14 ms·

Speeding up SQL queries by orders of magnitude using UNION

402 points by tate 6y ago

toast0 6y ago
UNION is also helpful for reducing round trips between the db client and db server. I've run into lots of cases where SQL join is clear to write, but slow, but a simple query to get ids, plus a union of fetching data for each id is fast. Again, a union of single id fetches is often faster than a single query with in (x, y, z). We can wish the sql engine could figure it out, but my experience from a few years ago is that they usually don't.
- onlyrealcuzzo 6y ago
  Does anyone with enough db experience know why the engine can't figure it out? Is it easy to explain? I'm not a db expert, but this seems like something that should be solvable.
  - bawolff 6y ago
    I imagine its bad query optimizer doing the wrong thing. Maybe a different db engine would do better.
    - slt2021 6y ago
      I dont like it when people who cannot write efficient SQL/design efficient database schema start blaming "bad" query optimizer. pro SQL developers extremely rarely have a problem with query optimizer, and when they have it is extremely exotic. it is usually ORM users that have a problem with SQL performance and blame the optimizer
      - bawolff 6y ago
        The comment i'm responding to is asking why two equivalent queries have different runtimes. The answer pretty obviously is because the optimizer is smart enough to chose the best query plan in one case and not the other. I think its fairly obvious that in theory a better optimizer could figure out the right plan in both cases. That's not to say that its impossible to work around or that in a real application that you would ever be "stuck" by this. At the end of the day you deal with the software you have, but the optimizer can still have weaknesses without it totally derailing your app.
        gfody 6y ago
        the optimizer is constrained, it still has to give you what you asked for. i can't even translate into english what's being asked for in query #2 but it's only a coincidence that it has the same result as the more precise and correct thing being asked for in the final query.
        lixtra 6y ago
        There are two possible cases: a) with everything known to the database (i.e. ids not null) the two queries produce the same result. b) for some contents of the schema the results differ, but in the presented case some additional condition (not known to the db) holds that makes them equivalent. Only in case b) it can be called coincidence. In case a) it’s fair to ask if the optimizer can’t do better. After all it tries to avoid cartesian products for simple joins even though that’s the text book definition. “Not worth” to improve the optimizer is still a valid answer.
        gfody 6y ago
        the query in question is definitely case b - it's a group by query with an explicit granularity, there's just no actual aggregates being asked for but the very presence or absence of a row in the results carries some meaning. the explicit grain is something like employee markouts plus customers from stores with at least one employee without markouts.
      - matwood 6y ago
        I agree they shouldn't start there, but you don't necessarily need exotic queries to run into edge cases that work differently across say MySQL, MSSQL, and Oracle. The classic example being exists vs left join null.
  - isoprophlex 6y ago
    Yeah agreed, at the very least the thing should be able to conceive to switch to this... lets call it "WHERE unrolling" internally when asked for " WHERE id IN (x, y, z)" I'd be delighted to learn what's going on
    - toast0 6y ago
      Where unrolling is an excellent term, thanks. The best I can understand is the engines are (or were) just not setup so they can do repeated single key lookups, which is really the right strategy for a where in. As a result, they do some kind of scan, which looks at a lot more data.
      - tpetry 6y ago
        The problem is these repeated single key lookups are random io for the database engine. So the database engine has to predict a threshold for when a scan or random io is cheaper which is very hard to get right, and your io layer changes this threshold drastically. A spinning disk may be faster all the time with sequential io, and for flash based systems theres a wide variety of performance profiles. To tackle this problem postgresql has a setting where you can tune the cost factor for random io.
        arp242 6y ago
        As an aside, this setting is almost certainly too high for you by default. The default of random_page_cost=4 assumes that random pages are 4 times more expensive to read than sequential pages, but with SSDs this cost is much much lower and chances are you're using an SSD these days. I managed to speed up queries by about 10x by just adjusting this. One Weird Trick To Speed Up Your PostgreSQL And Put DBAs Out Of a Job!
        guenthert 6y ago
        Thanks for the hint. I wonder where the value of 4 comes from. Is this an old value derived from a (then) fast RAID across many spinning rust disks? As you pointed out, today --- using SSDs -- the cost should be lower; I'd think in some cases (e.g. here a DB used for a catalog of a back-up system) the backing store is exactly one spinning rust drive, where I'd expect a much higher cost for random access (about 80MB/s / (100/s*8KiB)). Ah, [1] has the answer: they acknowledge that random accesses might be much slower, but expect a good share of accesses to be satisfied by the cache (that'll be a combination of various caches). [1] https://www.postgresql.org/docs/13/runtime-config-query.html https://www.postgresql.org/docs/13/runtime-config-query.html
  - btilly 6y ago
    I'm not a db expert, but this seems like something that should be solvable. It is very highly database specific. MySQL tends to be terrible. PostgreSQL tends to be quite good. The reasons why it did not figure it out in particular cases tend to be complicated. Except for MySQL, in which case the problem is simple. There is a tradeoff between time spent optimizing a query and time spent running it. Other databases assume that you prepare a query and then use it many times so can spend time on optimizations. MySQL has an application base of people who prepare and use queries just once. Which means that they use simple heuristics and can't afford the overhead of a complex search for a better plan.
    - kristianp 6y ago
      "MySQL has an application base of people who prepare and use queries just once". Is it really likely that mysql is unique in its usage patterns? Isn't it more likely that the devs have decided to keep things simple because that's the historical story for mysql. For example it originally didn't even support foreign key constraints if I recall correctly (that's a while ago now of course).
      - dragonwriter 6y ago
        > "MySQL has an application base of people who prepare and use queries just once". Is it really likely that mysql is unique in its usage patterns? It's quite plausible that it is distinct in the usage patterns it has chosen to optimize for, and that that design choice has been instrumental in determining what use cases it became popular for and which use cases led people to migrate off of it for a different engine, yes. To the extent that is at play here it is somewhat of an oversimplification (even if the target audience was a factor in the sewing decision) to describe that as simple unidirectional causation from usage to design, and possibly more accurate (though still oversimplified) the other way around.
      - btilly 6y ago
        Yeah, I'm pretty sure that it is unique to MySQL and SQLite. Back in the 90s and early 2000s, MySQL told people to just run queries. PHP encouraged the same. Every other database was competing on ability to run complex queries. And so it became common knowledge that applications written for MySQL didn't port well to, say, Oracle. Exactly because of this issue. Those applications and more like them are out there. And mostly expect to run on MySQL. So that use case has to be supported.
  - sixdimensional 6y ago
    There could be a lot of reasons that are highly engine dependent, for this specific case. A general answer, perhaps... not specific to the case you've specified. Query optimization is a science with multiple dimensions [1]. I'd wager every problem in computer science plays a role somehow in query optimization. Query performance is based on a combination of actions you take to optimize the design of your system to get the best performance (e.g. data modelling, index design, hardware, query style, and more), and the patterns the system can recognize based on your inputs and the data itself, with the resources it has available, to optimize your queries. There are known patterns for optimization that are discovered over the years, many hard learned from practical experience. This is why older "popular" engines sometimes are more mature and more performant - they have optimizations built for the common use cases over long periods of time. That is not to say older engines are always better, just that they have often had more exposure to the variety of problems that occur. The reason why the engine "can't figure it out" is that most engines, even the best ones, are quite complex - combinations of known rules as well as more fuzzy logic, where the engine uses a combination of information and heuristics to essentially explore a possible solution space, to try to find the optimal execution plan. Making the right decision, well, can be difficult and given the nature of these things, sometimes the optimizer makes the wrong decision (this is why "hints" exist, sometimes, you can force the optimizer to do what you see is obvious - but this is suboptimal for you). In some cases, finding an optimal execution plan can actually be quite computationally expensive, and/or quite time consuming, or the engine in question may simply have no logic coded to handle the case. Optimization is all about finding the balance between finding the most performant query plan, but in the least amount of time, with the least computational and I/O impact to the overall system, that returns the right result. Optimizers are also highly depending on the capabilities of the engineering teams that build them. It is not an easy problem, and it is an area which one could liken to almost machine learning/artificial intelligence, in one way. There are so many possible options, the problem space so big, with so many different ways to approach a given scenario, that it can be difficult for the "engine" to decide. This is why known patterns were created, for example, dimensional data models for analytical queries vs. 3rd normal form. Dimensional data models enable certain optimizations, for example, star schemas [2]. If you take a combination of implementing known patterns, along with optimizers written by engineers that exploit those patterns, you can get to a world of better performance. However, in a world that is, let's say.. more "open ended" - for example, the world of data in a "data lake", where data models are not optimized, data comes in unpredictable multiple shapes/sizes, then it often comes down to combinations of elegant/complex engines that can interpret the shapes of data, cardinality, and other characteristics, make use of much larger distributed compute and system performance, and in some cases - often brute force to arrive at the best query plan or performance possible. There are so many levels of optimization.. for example, if you were to look at things like Trino [3], which started its genesis as PrestoDb in Facebook - you will see special CPU optimizations (e.g. SIMD instructions), vectorized/pipelined operations - there are storage engine optimizations, memory optimizations, etc. etc. It truly is a complex and fascinating problem. Source: I was a technical product manager for a federated query engine. [1] https://en.wikipedia.org/wiki/Query_optimization https://en.wikipedia.org/wiki/Query_optimization [2] https://en.wikipedia.org/wiki/Star_schema https://en.wikipedia.org/wiki/Star_schema [3] https://trino.io/ https://trino.io/
- Daynil 6y ago
  I often read things that say that if you just write the query properly, you can trust the query optimizer to select the right plan. Just trust the optimizer! Unfortunately, in my experience, the more complex your query gets, the more opportunities the optimizer has to get it terribly wrong. I've learned to accept that, if the "clear/clean" version of the SQL (that the human likes to read and write) does not produce acceptable results, you just have to drop more explicit hints to the optimizer about what you want it to do. Query optimizers are truly awesome pieces of software, you just have to learn to work around their limitations when you hit them.
- ahmad_muzakkir 6y ago
  I'm very interested to learn what you are saying. Can you give an example ?
Justsignedup 6y ago
Just to note, the easiest heuristic I've been using to figure out when optimization might be useful is when you have an OR clause on more than one table. Column A or B is fine, but Table A column A or Table B column A is gonna cause interesting problems. Also: union = distinct values union all = no distinct
- croes 6y ago
  Important difference between union and union all, especially if you don't query a unique id column
  - deleted 6y ago
    [deleted]
fifilura 6y ago
Is what the article is essentially saying is that [1] is faster than [2]? I have used [1] many times for that reason although [2] is probably more intuitive for what you want to do. [1] SELECT vegetable_id, SUM(price) as price, SUM(weight) as weight FROM ( SELECT vegetable_id, price, NULL as weight FROM prices UNION ALL SELECT vegetable_id, NULL as price, weight FROM weights ) GROUP BY vegetable_id [2] SELECT vegetable_id, price, weight FROM prices JOIN weights ON price.vegetable_id = weights.vegetable_id
- croes 6y ago
  This queries have different results. [2] retrieves only the vegetable_ids which are in both tables, [1] gives all ids which are in prices or weights. If vegetable_id null exists in either table [1] result in an extra row with id null, this doesn't happen for query [2]
  - fifilura 6y ago
    If I replace JOIN with FULL OUTER JOIN, you'll get what you describe. It was just an quick example, but you are right. There are also things to say about what happens if either table has duplicate vegetable_id:s. At some point it is assumed that you have processed it in such a way that the table is well formed for the purpose.
- baix777 6y ago
  The relative performance of these two queries will vary by data volume. Swap in a sales table for the weights table, and make that a massive sales table at that, joining it to much smaller prices can be much faster than a group by. Stated differently, a join can be faster than a group by. This is even more true when the small table can fit into memory and a hash join can be used, and the data in the group by can't fit into memory.
  - fifilura 6y ago
    Yes I'd say that I would intuitively do that only if the tables were both sufficiently large. I assume somewhere there is a similar assumption in TFA.
tech2 6y ago
Perhaps I'm missing something, but why is query 2's meal_items a left join when the where clause then forces it to act as if it were an inner join? Would changing that have any impact?
- Albert_Camus 6y ago
  Author here, you are indeed correct that Query #2's final join can be an INNER join. However, I just tested it against our test data set and it makes no impact on the performance.
  - tech2 6y ago
    Ah well, thanks for humouring me. Good thing those query optimisers do the right thing on occasion.
croes 6y ago
No need to wrap the union queries in a new select to order the result. An order by after the last select in the unions orders the whole result set as long as the column names match in each part of the union.
- rrrrrrrrrrrryan 6y ago
  This depends on the database engine. The syntax will error out in some (perhaps most).
  - croes 6y ago
    Works in mysql, postgres, oracle, MS, DB2. Can you name an engine where it fails?
    - rrrrrrrrrrrryan 6y ago
      Seems I was completely wrong. I've learned something new today - thanks.
- AdrianB1 6y ago
  That works in MS SQL, not guaranteed in others.
  - croes 6y ago
    Works in mysql, postgres, oracle, MS, DB2
darig 6y ago
It would be even faster if you switched to a series of queries without any JOINs at all, and then stitched them together with code other than SQL after the queries have ran. Getting below 15 ms would be trivial.
CraigJPerry 6y ago
Is there any way to exploit parallelism in the customer vs employee queries then merge the results? My intuition would have been to tackle this in 3 steps with a temp table (create temp table with employee markouts, append customer meals, select ... order by). I can see the overheads of my solution but I’m wondering if i could beat the Union all approach somehow. My temp table wouldn’t benefit from an index so I’m left wondering about parallelism. If i remember correctly though, there’s no concurrency, and therefore no opportunity for parallelism for transactions in the same session.
- punnerud 6y ago
  Temp table as in using WITH?
  - Albert_Camus 6y ago
    Author of the original article here. Temporary tables are different than using WITH (which are common table expressions, or CTEs). In many database engines, can make a temporary table that will persist for a single session. The syntax is the same as table creation, it just starts with CREATE TEMPORARY TABLE .... More info in the PostgreSQL docs [1]: > If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well. [1]: https://www.postgresql.org/docs/13/sql-createtable.html https://www.postgresql.org/docs/13/sql-createtable.html
    - ewalk153 6y ago
      If this is a database for reporting, using a temporary table is probably fine and a union all wouldn’t concern me. On Mysql, using a union all creates a temp table which can perform catastrophically under database load. I’ve seen a union all query with zero rows in the second half render a database server unresponsive when the database was under high load, causing a service disruption. We ended rewriting the union all query as two database fetches and have not seen a single problem in that area since. I was shocked by this union all behavior, but it is apparently a well known thing on MySQL. I can’t speak to Postgres behavior for this kind of query.
twoodfin 6y ago
I know of at least one commercial DBMS that will perform this transformation automatically in some circumstances, and I’d be surprised if most of the other major systems couldn’t do the same.
- tpetry 6y ago
  All database systems could implement many more optimizations. Heck, all these tricks to get the queries to complete faster are only needed because the database engine did not find this fast execution plan. The problem is the more optimizations you implement the more possibilities the query planner has to evalue which may result in (1) a too long query planning phase or (2) the possibility of de-optimizing a good query because an optimization was falsely predicted to be faster
- posedge 6y ago
  Clever optimization engines are IMO no excuse for blindly throwing queries at it and not knowing what's going on. Every optimizer has some corner cases in which it fails and I'd argue that as a good developer you should know when they can happen. Additionally, in this example, the rewritten query is easier to read and maintain.
rrrrrrrrrrrryan 6y ago
Keep your matching logic simple. With SQL, matching is (by far) the most computationally expensive bit of most queries. Your goal should be to use only sargable[1] operators, and eliminate any functions, in all your WHERE clauses and JOINs. Complicated matching criteria will force the engine to make tough trade-off decisions, which can be affected by out-of-date table statistics, lousy indexes, and a million other things, and will often force the engine to rule-out more optimized algorithms and fall back to the cruder, primitive algorithms. [1] For most queries, "OR" is not usually a sargable operator: https://en.wikipedia.org/wiki/Sargable https://en.wikipedia.org/wiki/Sargable
- setr 6y ago
  Shouldn’t OR operations in an otherwise sargable[0] query just constitute merging two sargable queries? I don’t see why OR should affect the property [0] new term for me, and wow do I not like it
  - kroolik 6y ago
    This depends on the rdbms engine. Even if it might make sense, the planned could go with nested loops because of statistics and cost estimation. In the article, the devil is in how the second explodes in size. We are suddenly cross-joining employees with customers. Left join of stores, employees and markouts gives you more than 2 rows. It gives you all the employees assigned to the store. Regardless only 2 have markouts. Next, you join full customers table with it. And keep on multiplying the size by further left joining. Result size might quickly exceed the memory limits for efficient joins, and the rdbms might have to resort to poor nested loops.
- dragonwriter 6y ago
  > Your goal should be to use only sargable[1] operators, and eliminate any functions, in all your WHERE clauses and JOINs. Or just use indexes properly; it's fairly common for modern databases to support indexes on functions of column values; using an indexed (deterministic) function in WHERE/JOIN criteria isn’t problematic. The Wikipedia article you link addresses valid concerns, but the specifics it suggests don't seem accurate for the features of modern RDBMSs.
clcaev 6y ago
The author doesn't describe why they thought a cross product of two large tables, using a group by to remove duplicates, would be a good basis for this query.
- gfody 6y ago
  The author(s?) almost certainly have no idea what they're talking about. Wtf are "diamond schemas" and "multi-branch joins"? This blog is the sole google result for that made up nonsense that's supposedly there to imply that it's somehow normal to put an OR expression with columns from two different tables into a join clause. They're using select * in a group by query with no aggregates. And they're talking about keywords as operations as if their SQL is directly executed and doing stuff rather than merely being the input for a plan generator (this despite the first paragraph - maybe one of them knows better than the other?). They say that query #2 is "the intuitive way of thinking through the problem, and something that someone with intermediate or advanced SQL skills could come up with" and is slow because it "eliminated the optimizer's ability to create an efficient query plan" - and they're wrong on both counts. That query is a nightmare no advanced SQL expert would write, and the plan that was generated was probably perfectly optimal to give them what they asked for. They just don't realize they asked for ridiculous nonsense that accidentally matches their expected results.
  - idoubtit 6y ago
    > The author(s?) almost certainly have no idea what they're talking about. > They're using select * in a group by query with no aggregates. The author wrote something akin to SELECT A.*, B.x FROM A JOIN B GROUP BY A.id, b.x This is perfectly valid SQL, AS per the SQL standard: "Each <column reference> in the <group by clause> shall unambiguously reference a column of T. A column referenced in a <group by clause> is a grouping column". From SQL-92 reference, section 7.7, http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt When using offensive language, you'd better be sure of the technical quality of what you write, if you don't want to show an ugly portrait of yourself.
    - 6y ago
balfirevic 6y ago
I'm now curious (but too lazy to setup and test it) how the following query would fare: SELECT meal_items.*, employee_markouts.employee_id, customer_orders.customer_id FROM meal_items LEFT JOIN employee_markouts ON employee_markouts.meal_item_id = meal_items.id LEFT JOIN employees ON employees.id = employee_markouts.employee_id LEFT JOIN customer_order_items ON customer_order_items.meal_item_id = meal_items.id LEFT JOIN customer_orders ON customer_orders.id = customer_order_items.customer_order_id LEFT JOIN customers ON customers.id = customer_orders.customer_id WHERE (employees.store_id = 250 AND employee_markouts.created >= '2021-02-03' AND employee_markouts.created < '2021-02-04') OR (customers.store_id = 250 AND customer_orders.created >= '2021-02-03' AND customer_orders.created < '2021-02-04')
- knuthsat 6y ago
  OR really messes things up. UNION instead makes things fast.
  - balfirevic 6y ago
    Oh, I wouldn't presume that it will be faster than the union but I wonder how it compares to the original query in the article (the one with OR).
- Albert_Camus 6y ago
  Author here. This doesn't give the correct results. It produces meal_items that have both customer_id and employee_id. Here's an excerpt (the full result set is thousands of rows, as opposed to the expected 45): id |label |price|employee_id|customer_id| ---|------------|-----|-----------|-----------| ... 344|Poke | 4.18| 3772| 13204| 344|Poke | 4.18| 3313| 13204| 344|Poke | 4.18| 2320| 13204| 344|Poke | 4.18| 632| 13204| 344|Poke | 4.18| 4264| 13204| 344|Poke | 4.18| 699| 13204| 344|Poke | 4.18| 1070| 13204| 344|Poke | 4.18| 3022| 13204| 344|Poke | 4.18| 1501| 13204| 344|Poke | 4.18| 808| 13204| 344|Poke | 4.18| 2793| 13204| 344|Poke | 4.18| 1660| 13204| 344|Poke | 4.18| 932| 13204| ... To be clear, there are ways to write this query without UNION that have both good performance and give the correct results, but they're very fiddly and harder to reason about that just writing the two comparatively simple queries and then mashing the results together.
gfody 6y ago
> Retrieving both employee and customer meal items When you're getting two separate sets of things in one query you should expect to use union. This isn't so much a performance optimization as just expressing the query properly. Imagine starting with just this weird SQL that gets both employee and customer meal items using only joins, and then trying to infer the original intent.
kroolik 6y ago
The author didn't mention the reason the query is slow. I dare to challenge that the non-optimal query is something that a person with advanced SQL skills would do. A seasoned engineer knows that by joining two tables, you are looking at, worst case, NxM comparisons. The problem is that you are joining the customers table with THREE other tables - the merged result of stores, employees and markouts. No matter how hard you try, you can't escape the fact that you are joining way more rows than just stores and customers. I would call it a layman issue SQL beginners learn the hard way. Advanced SQL users know the very basics of the db they are working with.
- jiggawatts 6y ago
  I don't even understand the thought process that would have led to the non-optimal query. It would simply never occur to me to use anything other than UNION ALL for this kind of scenario, because it is the only appropriate translation of the english-language business request into a SQL query.
  - dwd 6y ago
    After working 20+ years in the industry I'm always surprised by the aversion (or lack of thought) to draw up a Venn diagram on a white board to visualise the relation, let alone then write it out using set notation.
  - avereveard 6y ago
    Probably the result of some early attempt at either sorting or removing duplicates
  - robertlagrant 6y ago
    It could be that the query was gradually modified. It's pretty simple to spot the union when the problem's being focused on, laid out nicely in a blog post, and the answer already given!
- ncallaway 6y ago
  > I would call it a layman issue SQL beginners learn the hard way. Advanced SQL users know the very basics of the db they are working with. So? Does every blog post have to be for expert users of the system? What's wrong with a blog post that explains mistakes that a beginner might make, and a method for mitigating it.
Black101 6y ago
speeding up sql queries isnt hard but it takes time and its worth it.
magicalhippo 6y ago
At work I ended up with something similar. A reporting query has about 20 sum()'ed columns, and found the easiest and by far fastest was just to have 20+ queries glued by UNION ALL, and aggregate the sums over that. It's heavy in it's raw form, but if you add some search terms to narrow things down the DB really cuts through. It's about 320 lines of SQL, so to make debugging a bit easier I added a dummy "identifier" column to each of the union'ed sub-queries, so first would have value 100, second 200 etc, so I could quickly identify which of the sub-queries produced a given row. Not counting in 1's allowed for easy insertion of additional sub-queries near relevant "identifiers".
danvk 6y ago
Why is it so important to express this as one SQL query? Why not just run the two simple queries? It feels as though two different types of things are being joined.
- Daynil 6y ago
  Depends on your goals. If you're a software engineer, it won't make a difference, just send two queries to the DB and combine the results before sending them off. If you're an analyst, you'll often get repeated ad-hoc requests. If you have to run 2 queries, then stitch them together, each time you are asked for a report, you're wasting tons of (really boring) time. Even if you ultimately create some code to run and stitch the results together for you, it's just faster and easier to write 1 query wherever possible. Just let the database do the work!
  - Albert_Camus 6y ago
    > If you're a software engineer, it won't make a difference, just send two queries to the DB and combine the results before sending them off. This isn't true if you have certain use cases in your application, pagination being one of them. There's no simple way to implement pagination when you have two or more queries that return and unknown number of results and you're tasked with maintaining a consistent order across page changes. If you make a single query do all the work, it's easy to implement paging in any number of ways, the most performant being to filter by the last id the client saw. If your users aren't likely to paginate too far into the data, LIMIT and OFFSET will work fine as well.
- drstewart 6y ago
  You're falling into the trap of thinking like the developer looking at the implementation, not the end user. The goal is: >In order to audit inventory, the logistics team at the corporate headquarters requests a tool that can generate a report containing all meal_items that left a given store’s inventory on a particular day That's one thing. The end user doesn't see these as two separate requests just because that's how it's modeled in the database.
slt2021 6y ago
This is ABC of SQL: every SQL developer has to learn this lesson soon or later: Every additional JOIN you make creates a cartesian product of all previously joined records and the number of rows grows exponentially => you join tables A to B to C to D -> SQL engine has to create AxBxCxD number rows, each letter representing number of rows in that table) Of course specifying useful predicates that filter down dataset and having indexes on these columns helps avoid row explosion, but you can't have an index on each and every column, especially on OLTP database. in example #2 the author joined 7 tables (multiply row count of 7 tables and this gives you an indea of how many rows SQL engine has to churn through) - big O complexity of query is (AxBxCxDxExFxG) of course it will be SLOW. in example #4 he joins 4 tables and unions with 5 table join, so the big O complexity of query is AxBxCxD(1+E). same as in programming, there is Big O complexity in your SQL queries, so it helps to know O() compelxity of queries that you write TLDR: learn the SQL Big-O and stop compaining about the query planner, pls
- tehlike 6y ago
  Something that helps with exploding cartesian problem is using json_agg for the joined tables. IT's available in postgres, it works great.
- meritt 6y ago
  Your understanding is flawed, these aren’t cross joins.
- jaredsohn 6y ago
  I've found denormalization to be helpful for such performance issues in that it reduces the number of joins. It can also be useful for debugging if you want to keep track of values from other tables at the time that data was processed within the primary table.
  - slt2021 6y ago
    denormalization aka memoization aka "dynamic programming of SQL" :-) - all three provide speed-up because they compute value once and reuse it later
deleted 6y ago
[deleted]
boulos 6y ago
Huh. Like other commenters, I’m surprised to see a multi part JOIN be the answer for “I want the results of Query A as well as the results of Query B”. If anything, I think many folks would say “whatever, make the two requests in parallel and zip them together in the application”. If not, WITH expressions to make the two independent queries and then a final SELECT to merge them however you prefer would be reasonable (whether that’s a join flavor or UNION/INTERSECT/etc.), but the super explosion JOIN just doesn’t cross my mind. I think there must be a different example that they tried to anonymize for this post, otherwise they don’t need the IDs (and probably want a sum of quantity). If the author is around, what was the more realistic example behind: “In order to audit inventory, the logistics team at the corporate headquarters requests a tool that can generate a report containing all meal_items that left a given store’s inventory on a particular day. This requires a query that includes items that were both sold to customers as well as recorded as employee markouts for the specified store on the specified day.”?
- IgorPartola 6y ago
  Where this fail (and I don’t see the article addressing this concern) is if you want to do pagination. Say you are retrieving page 17 of 8000. Will the UNION still be performant then? After all, it will require a stable sort order that may depend on one of the branches. You certainly can’t pagination without doing a whole lot of logic in your application if you use two separate queries. My typical solution to this is to do a query not employee IDs, another one of customer IDs and then do an IN clause. Or you could do an intermediate ID of some kind that will collapse your diamond branch into a single IN.
  - catmanjan 6y ago
    In my experience this is the number one driver for infinite scroll, dropping paging simplifies a lot
    - sahila 6y ago
      Having infinite scroll doesn't avoid paging results from a query. Like the OP mentioned, if you have 8000 page * results/page records, you may only want to retrieve 50 records at a time to pass back to the client. In that case, you will still need a way to query for the next set / page. Infinite scroll is a UX implementation.
jjtheblunt 6y ago
I wonder if i'm the only one who sees the phrase "order(s) of magnitude" and expects a vague story. I don't mean to denigrate this article, just wonder if others have the same reaction.
mongol 6y ago
What is the best book to learn performant SQL querying? I recently needed to query a large database for exploratory purposes. The database has historical data about many years of sales transactions of a huge company. It is using partitioning keys. I know pretty well how to write queries to get what I want but given the size of the database I need to write them performantly. How can I learn that best?
- sqldba 6y ago
  SQL Performance Explained, hands-down https://sql-performance-explained.com/ https://sql-performance-explained.com/ Covers all the main DBMS, explained very simply from the bottom up, maintained constantly and has been read and recommended by people for years and years.
noisy_boy 6y ago
Everyone who the store is selling to is a customer; an employee is a customer too except the price might be zero. What if a field is added to customers table: employee_id (would be null by default as most customers would not be employee). Then the query can be run without any union and bypassing stores/employees/employee_markouts etc: SELECT meal_items.*, customer_id, customers.employee_id FROM customers INNER JOIN customer_orders ON customers.id = customer_orders.customer_id INNER JOIN customer_order_items ON customer_orders.id = customer_order_items.customer_order_id INNER JOIN meal_items ON customer_order_items.meal_item_id = meal_items.id Of course this means that customer_id will never be null but that kind of is the point. This can also be approached by adding a customer_id field to the employees table instead (in this example the no. of employees and customers are both 20000 but typically the no. of employees is less than the no. of customers so adding customer_id to employees table would be more typical - in which case the query would obviously change).
- kroolik 6y ago
  I think this is called denormalization, right? What you could try is to instead of modifying the domain for this specific use-case, create a read model instead (if you don't want to pollute your domain) . Sometimes its very easy to write data, but reading is a pain. For such cases, you can prepare read models that contains all the data necessary.
  - noisy_boy 6y ago
    This is not specifically denormalization because I'm not combining/flattening two models - instead, I'm moving the special treatment for employees from a totally separate path to a property of the existing customer model without removing/merging the employee model. Regarding read optimizations, there are several ways, some of which are vendor specific e.g. PostgreSQL/Oracle allow materialized views which can make accessing this data simple (if often required though they have their own issues).
  - deleted 6y ago
    [deleted]
gigatexal 6y ago
Would be cool to post the sqldump of the data somewhere so we could play with the queries and the database with the same data.
blunte 6y ago
Not speaking negatively about the author of this post, but union should have been the obvious tool to use in a scenario like this. SQL is very powerful and really not so complicated. Schema, index, and constraint design can be quite challenging, depending on the needs, but that's true whether using SQL or an ORM. The problem I see often is that people use ORMs and never learn SQL. Also often, one has to really work to understand how their ORM is handling certain situation to avoid problems like N+1 or cartesian products. In the end, it seems that rather than learning SQL, the developer ends up learning just as much or more about a specific ORM to get the same quality/performing results they would have gotten by using direct SQL.
02020202 6y ago
i use projections so simple query fuck the what.
zeroimpl 6y ago
I suspect query #2 would have performed better if they used a COALESCE instead of an OR condition. One of the most important optimization lessons you can learn is not to use OR for any join conditions. Eg instead of this: ON (customer_order_items.meal_item_id = meal_items.id OR employee_markouts.meal_item_id = meal_items.id ) Do this: ON meal_items.id = COALESCE( employee_markouts.meal_item_id, customer_order_items.meal_item_id ) The latter can optimize properly. Probably still slower than the UNION, but would be more in line with expected performance. It might be more useful too in certain cases, such as if building a view.
muad_kyrlach 6y ago
Am I the only one who rejects this data model? Customers belong to store and orders don't? I can see how a customer might have a primary store, but i would link the customer to the order and link the order to the store. Union's are awesome, but proper data modeling is better. :)
stremovsky 6y ago
Similar article of mine from 2013: https://cloudinvent.com/blog/optimize-mysql-count-query/ https://cloudinvent.com/blog/optimize-mysql-count-query/

402 points by tate 6y ago

UNION is also helpful for reducing round trips between the db client and db server. I've run into lots of cases where SQL join is clear to write, but slow, but a simple query to get ids, plus a union of fetching data for each id is fast. Again, a union of single id fetches is often faster than a single query with in (x, y, z). We can wish the sql engine could figure it out, but my experience from a few years ago is that they usually don't.

onlyrealcuzzo 6y ago
Does anyone with enough db experience know why the engine can't figure it out? Is it easy to explain? I'm not a db expert, but this seems like something that should be solvable.
- bawolff 6y ago
  I imagine its bad query optimizer doing the wrong thing. Maybe a different db engine would do better.
  - slt2021 6y ago
    I dont like it when people who cannot write efficient SQL/design efficient database schema start blaming "bad" query optimizer. pro SQL developers extremely rarely have a problem with query optimizer, and when they have it is extremely exotic. it is usually ORM users that have a problem with SQL performance and blame the optimizer
    - bawolff 6y ago
      The comment i'm responding to is asking why two equivalent queries have different runtimes. The answer pretty obviously is because the optimizer is smart enough to chose the best query plan in one case and not the other. I think its fairly obvious that in theory a better optimizer could figure out the right plan in both cases. That's not to say that its impossible to work around or that in a real application that you would ever be "stuck" by this. At the end of the day you deal with the software you have, but the optimizer can still have weaknesses without it totally derailing your app.
      - gfody 6y ago
        the optimizer is constrained, it still has to give you what you asked for. i can't even translate into english what's being asked for in query #2 but it's only a coincidence that it has the same result as the more precise and correct thing being asked for in the final query.
        lixtra 6y ago
        There are two possible cases: a) with everything known to the database (i.e. ids not null) the two queries produce the same result. b) for some contents of the schema the results differ, but in the presented case some additional condition (not known to the db) holds that makes them equivalent. Only in case b) it can be called coincidence. In case a) it’s fair to ask if the optimizer can’t do better. After all it tries to avoid cartesian products for simple joins even though that’s the text book definition. “Not worth” to improve the optimizer is still a valid answer.
        gfody 6y ago
        the query in question is definitely case b - it's a group by query with an explicit granularity, there's just no actual aggregates being asked for but the very presence or absence of a row in the results carries some meaning. the explicit grain is something like employee markouts plus customers from stores with at least one employee without markouts.
    - matwood 6y ago
      I agree they shouldn't start there, but you don't necessarily need exotic queries to run into edge cases that work differently across say MySQL, MSSQL, and Oracle. The classic example being exists vs left join null.
- isoprophlex 6y ago
  Yeah agreed, at the very least the thing should be able to conceive to switch to this... lets call it "WHERE unrolling" internally when asked for " WHERE id IN (x, y, z)" I'd be delighted to learn what's going on
  - toast0 6y ago
    Where unrolling is an excellent term, thanks. The best I can understand is the engines are (or were) just not setup so they can do repeated single key lookups, which is really the right strategy for a where in. As a result, they do some kind of scan, which looks at a lot more data.
    - tpetry 6y ago
      The problem is these repeated single key lookups are random io for the database engine. So the database engine has to predict a threshold for when a scan or random io is cheaper which is very hard to get right, and your io layer changes this threshold drastically. A spinning disk may be faster all the time with sequential io, and for flash based systems theres a wide variety of performance profiles. To tackle this problem postgresql has a setting where you can tune the cost factor for random io.
      - arp242 6y ago
        As an aside, this setting is almost certainly too high for you by default. The default of random_page_cost=4 assumes that random pages are 4 times more expensive to read than sequential pages, but with SSDs this cost is much much lower and chances are you're using an SSD these days. I managed to speed up queries by about 10x by just adjusting this. One Weird Trick To Speed Up Your PostgreSQL And Put DBAs Out Of a Job!
        guenthert 6y ago
        Thanks for the hint. I wonder where the value of 4 comes from. Is this an old value derived from a (then) fast RAID across many spinning rust disks? As you pointed out, today --- using SSDs -- the cost should be lower; I'd think in some cases (e.g. here a DB used for a catalog of a back-up system) the backing store is exactly one spinning rust drive, where I'd expect a much higher cost for random access (about 80MB/s / (100/s*8KiB)). Ah, [1] has the answer: they acknowledge that random accesses might be much slower, but expect a good share of accesses to be satisfied by the cache (that'll be a combination of various caches). [1] https://www.postgresql.org/docs/13/runtime-config-query.html https://www.postgresql.org/docs/13/runtime-config-query.html
- btilly 6y ago
  I'm not a db expert, but this seems like something that should be solvable. It is very highly database specific. MySQL tends to be terrible. PostgreSQL tends to be quite good. The reasons why it did not figure it out in particular cases tend to be complicated. Except for MySQL, in which case the problem is simple. There is a tradeoff between time spent optimizing a query and time spent running it. Other databases assume that you prepare a query and then use it many times so can spend time on optimizations. MySQL has an application base of people who prepare and use queries just once. Which means that they use simple heuristics and can't afford the overhead of a complex search for a better plan.
  - kristianp 6y ago
    "MySQL has an application base of people who prepare and use queries just once". Is it really likely that mysql is unique in its usage patterns? Isn't it more likely that the devs have decided to keep things simple because that's the historical story for mysql. For example it originally didn't even support foreign key constraints if I recall correctly (that's a while ago now of course).
    - dragonwriter 6y ago
      > "MySQL has an application base of people who prepare and use queries just once". Is it really likely that mysql is unique in its usage patterns? It's quite plausible that it is distinct in the usage patterns it has chosen to optimize for, and that that design choice has been instrumental in determining what use cases it became popular for and which use cases led people to migrate off of it for a different engine, yes. To the extent that is at play here it is somewhat of an oversimplification (even if the target audience was a factor in the sewing decision) to describe that as simple unidirectional causation from usage to design, and possibly more accurate (though still oversimplified) the other way around.
    - btilly 6y ago
      Yeah, I'm pretty sure that it is unique to MySQL and SQLite. Back in the 90s and early 2000s, MySQL told people to just run queries. PHP encouraged the same. Every other database was competing on ability to run complex queries. And so it became common knowledge that applications written for MySQL didn't port well to, say, Oracle. Exactly because of this issue. Those applications and more like them are out there. And mostly expect to run on MySQL. So that use case has to be supported.
- sixdimensional 6y ago
  There could be a lot of reasons that are highly engine dependent, for this specific case. A general answer, perhaps... not specific to the case you've specified. Query optimization is a science with multiple dimensions [1]. I'd wager every problem in computer science plays a role somehow in query optimization. Query performance is based on a combination of actions you take to optimize the design of your system to get the best performance (e.g. data modelling, index design, hardware, query style, and more), and the patterns the system can recognize based on your inputs and the data itself, with the resources it has available, to optimize your queries. There are known patterns for optimization that are discovered over the years, many hard learned from practical experience. This is why older "popular" engines sometimes are more mature and more performant - they have optimizations built for the common use cases over long periods of time. That is not to say older engines are always better, just that they have often had more exposure to the variety of problems that occur. The reason why the engine "can't figure it out" is that most engines, even the best ones, are quite complex - combinations of known rules as well as more fuzzy logic, where the engine uses a combination of information and heuristics to essentially explore a possible solution space, to try to find the optimal execution plan. Making the right decision, well, can be difficult and given the nature of these things, sometimes the optimizer makes the wrong decision (this is why "hints" exist, sometimes, you can force the optimizer to do what you see is obvious - but this is suboptimal for you). In some cases, finding an optimal execution plan can actually be quite computationally expensive, and/or quite time consuming, or the engine in question may simply have no logic coded to handle the case. Optimization is all about finding the balance between finding the most performant query plan, but in the least amount of time, with the least computational and I/O impact to the overall system, that returns the right result. Optimizers are also highly depending on the capabilities of the engineering teams that build them. It is not an easy problem, and it is an area which one could liken to almost machine learning/artificial intelligence, in one way. There are so many possible options, the problem space so big, with so many different ways to approach a given scenario, that it can be difficult for the "engine" to decide. This is why known patterns were created, for example, dimensional data models for analytical queries vs. 3rd normal form. Dimensional data models enable certain optimizations, for example, star schemas [2]. If you take a combination of implementing known patterns, along with optimizers written by engineers that exploit those patterns, you can get to a world of better performance. However, in a world that is, let's say.. more "open ended" - for example, the world of data in a "data lake", where data models are not optimized, data comes in unpredictable multiple shapes/sizes, then it often comes down to combinations of elegant/complex engines that can interpret the shapes of data, cardinality, and other characteristics, make use of much larger distributed compute and system performance, and in some cases - often brute force to arrive at the best query plan or performance possible. There are so many levels of optimization.. for example, if you were to look at things like Trino [3], which started its genesis as PrestoDb in Facebook - you will see special CPU optimizations (e.g. SIMD instructions), vectorized/pipelined operations - there are storage engine optimizations, memory optimizations, etc. etc. It truly is a complex and fascinating problem. Source: I was a technical product manager for a federated query engine. [1] https://en.wikipedia.org/wiki/Query_optimization https://en.wikipedia.org/wiki/Query_optimization [2] https://en.wikipedia.org/wiki/Star_schema https://en.wikipedia.org/wiki/Star_schema [3] https://trino.io/ https://trino.io/
Daynil 6y ago
I often read things that say that if you just write the query properly, you can trust the query optimizer to select the right plan. Just trust the optimizer! Unfortunately, in my experience, the more complex your query gets, the more opportunities the optimizer has to get it terribly wrong. I've learned to accept that, if the "clear/clean" version of the SQL (that the human likes to read and write) does not produce acceptable results, you just have to drop more explicit hints to the optimizer about what you want it to do. Query optimizers are truly awesome pieces of software, you just have to learn to work around their limitations when you hit them.
ahmad_muzakkir 6y ago
I'm very interested to learn what you are saying. Can you give an example ?

Justsignedup 6y ago

Just to note, the easiest heuristic I've been using to figure out when optimization might be useful is when you have an OR clause on more than one table. Column A or B is fine, but Table A column A or Table B column A is gonna cause interesting problems. Also: union = distinct values union all = no distinct

croes 6y ago
Important difference between union and union all, especially if you don't query a unique id column
- deleted 6y ago
  [deleted]

fifilura 6y ago

Is what the article is essentially saying is that [1] is faster than [2]? I have used [1] many times for that reason although [2] is probably more intuitive for what you want to do. [1] SELECT vegetable_id, SUM(price) as price, SUM(weight) as weight FROM ( SELECT vegetable_id, price, NULL as weight FROM prices UNION ALL SELECT vegetable_id, NULL as price, weight FROM weights ) GROUP BY vegetable_id [2] SELECT vegetable_id, price, weight FROM prices JOIN weights ON price.vegetable_id = weights.vegetable_id

croes 6y ago
This queries have different results. [2] retrieves only the vegetable_ids which are in both tables, [1] gives all ids which are in prices or weights. If vegetable_id null exists in either table [1] result in an extra row with id null, this doesn't happen for query [2]
- fifilura 6y ago
  If I replace JOIN with FULL OUTER JOIN, you'll get what you describe. It was just an quick example, but you are right. There are also things to say about what happens if either table has duplicate vegetable_id:s. At some point it is assumed that you have processed it in such a way that the table is well formed for the purpose.
baix777 6y ago
The relative performance of these two queries will vary by data volume. Swap in a sales table for the weights table, and make that a massive sales table at that, joining it to much smaller prices can be much faster than a group by. Stated differently, a join can be faster than a group by. This is even more true when the small table can fit into memory and a hash join can be used, and the data in the group by can't fit into memory.
- fifilura 6y ago
  Yes I'd say that I would intuitively do that only if the tables were both sufficiently large. I assume somewhere there is a similar assumption in TFA.

tech2 6y ago

Perhaps I'm missing something, but why is query 2's meal_items a left join when the where clause then forces it to act as if it were an inner join? Would changing that have any impact?

Albert_Camus 6y ago
Author here, you are indeed correct that Query #2's final join can be an INNER join. However, I just tested it against our test data set and it makes no impact on the performance.
- tech2 6y ago
  Ah well, thanks for humouring me. Good thing those query optimisers do the right thing on occasion.

croes 6y ago

No need to wrap the union queries in a new select to order the result. An order by after the last select in the unions orders the whole result set as long as the column names match in each part of the union.

rrrrrrrrrrrryan 6y ago
This depends on the database engine. The syntax will error out in some (perhaps most).
- croes 6y ago
  Works in mysql, postgres, oracle, MS, DB2. Can you name an engine where it fails?
  - rrrrrrrrrrrryan 6y ago
    Seems I was completely wrong. I've learned something new today - thanks.
AdrianB1 6y ago
That works in MS SQL, not guaranteed in others.
- croes 6y ago
  Works in mysql, postgres, oracle, MS, DB2

darig 6y ago

It would be even faster if you switched to a series of queries without any JOINs at all, and then stitched them together with code other than SQL after the queries have ran. Getting below 15 ms would be trivial.

CraigJPerry 6y ago

Is there any way to exploit parallelism in the customer vs employee queries then merge the results? My intuition would have been to tackle this in 3 steps with a temp table (create temp table with employee markouts, append customer meals, select ... order by). I can see the overheads of my solution but I’m wondering if i could beat the Union all approach somehow. My temp table wouldn’t benefit from an index so I’m left wondering about parallelism. If i remember correctly though, there’s no concurrency, and therefore no opportunity for parallelism for transactions in the same session.

punnerud 6y ago
Temp table as in using WITH?
- Albert_Camus 6y ago
  Author of the original article here. Temporary tables are different than using WITH (which are common table expressions, or CTEs). In many database engines, can make a temporary table that will persist for a single session. The syntax is the same as table creation, it just starts with CREATE TEMPORARY TABLE .... More info in the PostgreSQL docs [1]: > If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well. [1]: https://www.postgresql.org/docs/13/sql-createtable.html https://www.postgresql.org/docs/13/sql-createtable.html
  - ewalk153 6y ago
    If this is a database for reporting, using a temporary table is probably fine and a union all wouldn’t concern me. On Mysql, using a union all creates a temp table which can perform catastrophically under database load. I’ve seen a union all query with zero rows in the second half render a database server unresponsive when the database was under high load, causing a service disruption. We ended rewriting the union all query as two database fetches and have not seen a single problem in that area since. I was shocked by this union all behavior, but it is apparently a well known thing on MySQL. I can’t speak to Postgres behavior for this kind of query.

twoodfin 6y ago

I know of at least one commercial DBMS that will perform this transformation automatically in some circumstances, and I’d be surprised if most of the other major systems couldn’t do the same.

tpetry 6y ago
All database systems could implement many more optimizations. Heck, all these tricks to get the queries to complete faster are only needed because the database engine did not find this fast execution plan. The problem is the more optimizations you implement the more possibilities the query planner has to evalue which may result in (1) a too long query planning phase or (2) the possibility of de-optimizing a good query because an optimization was falsely predicted to be faster
posedge 6y ago
Clever optimization engines are IMO no excuse for blindly throwing queries at it and not knowing what's going on. Every optimizer has some corner cases in which it fails and I'd argue that as a good developer you should know when they can happen. Additionally, in this example, the rewritten query is easier to read and maintain.

rrrrrrrrrrrryan 6y ago

Keep your matching logic simple. With SQL, matching is (by far) the most computationally expensive bit of most queries. Your goal should be to use only sargable[1] operators, and eliminate any functions, in all your WHERE clauses and JOINs. Complicated matching criteria will force the engine to make tough trade-off decisions, which can be affected by out-of-date table statistics, lousy indexes, and a million other things, and will often force the engine to rule-out more optimized algorithms and fall back to the cruder, primitive algorithms. [1] For most queries, "OR" is not usually a sargable operator: https://en.wikipedia.org/wiki/Sargable https://en.wikipedia.org/wiki/Sargable

setr 6y ago
Shouldn’t OR operations in an otherwise sargable[0] query just constitute merging two sargable queries? I don’t see why OR should affect the property [0] new term for me, and wow do I not like it
- kroolik 6y ago
  This depends on the rdbms engine. Even if it might make sense, the planned could go with nested loops because of statistics and cost estimation. In the article, the devil is in how the second explodes in size. We are suddenly cross-joining employees with customers. Left join of stores, employees and markouts gives you more than 2 rows. It gives you all the employees assigned to the store. Regardless only 2 have markouts. Next, you join full customers table with it. And keep on multiplying the size by further left joining. Result size might quickly exceed the memory limits for efficient joins, and the rdbms might have to resort to poor nested loops.
dragonwriter 6y ago
> Your goal should be to use only sargable[1] operators, and eliminate any functions, in all your WHERE clauses and JOINs. Or just use indexes properly; it's fairly common for modern databases to support indexes on functions of column values; using an indexed (deterministic) function in WHERE/JOIN criteria isn’t problematic. The Wikipedia article you link addresses valid concerns, but the specifics it suggests don't seem accurate for the features of modern RDBMSs.

clcaev 6y ago

The author doesn't describe why they thought a cross product of two large tables, using a group by to remove duplicates, would be a good basis for this query.

gfody 6y ago
The author(s?) almost certainly have no idea what they're talking about. Wtf are "diamond schemas" and "multi-branch joins"? This blog is the sole google result for that made up nonsense that's supposedly there to imply that it's somehow normal to put an OR expression with columns from two different tables into a join clause. They're using select * in a group by query with no aggregates. And they're talking about keywords as operations as if their SQL is directly executed and doing stuff rather than merely being the input for a plan generator (this despite the first paragraph - maybe one of them knows better than the other?). They say that query #2 is "the intuitive way of thinking through the problem, and something that someone with intermediate or advanced SQL skills could come up with" and is slow because it "eliminated the optimizer's ability to create an efficient query plan" - and they're wrong on both counts. That query is a nightmare no advanced SQL expert would write, and the plan that was generated was probably perfectly optimal to give them what they asked for. They just don't realize they asked for ridiculous nonsense that accidentally matches their expected results.
- idoubtit 6y ago
  > The author(s?) almost certainly have no idea what they're talking about. > They're using select * in a group by query with no aggregates. The author wrote something akin to SELECT A.*, B.x FROM A JOIN B GROUP BY A.id, b.x This is perfectly valid SQL, AS per the SQL standard: "Each <column reference> in the <group by clause> shall unambiguously reference a column of T. A column referenced in a <group by clause> is a grouping column". From SQL-92 reference, section 7.7, http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt When using offensive language, you'd better be sure of the technical quality of what you write, if you don't want to show an ugly portrait of yourself.
  - 6y ago

balfirevic 6y ago

I'm now curious (but too lazy to setup and test it) how the following query would fare: SELECT meal_items.*, employee_markouts.employee_id, customer_orders.customer_id FROM meal_items LEFT JOIN employee_markouts ON employee_markouts.meal_item_id = meal_items.id LEFT JOIN employees ON employees.id = employee_markouts.employee_id LEFT JOIN customer_order_items ON customer_order_items.meal_item_id = meal_items.id LEFT JOIN customer_orders ON customer_orders.id = customer_order_items.customer_order_id LEFT JOIN customers ON customers.id = customer_orders.customer_id WHERE (employees.store_id = 250 AND employee_markouts.created >= '2021-02-03' AND employee_markouts.created < '2021-02-04') OR (customers.store_id = 250 AND customer_orders.created >= '2021-02-03' AND customer_orders.created < '2021-02-04')

knuthsat 6y ago
OR really messes things up. UNION instead makes things fast.
- balfirevic 6y ago
  Oh, I wouldn't presume that it will be faster than the union but I wonder how it compares to the original query in the article (the one with OR).
Albert_Camus 6y ago
Author here. This doesn't give the correct results. It produces meal_items that have both customer_id and employee_id. Here's an excerpt (the full result set is thousands of rows, as opposed to the expected 45): id |label |price|employee_id|customer_id| ---|------------|-----|-----------|-----------| ... 344|Poke | 4.18| 3772| 13204| 344|Poke | 4.18| 3313| 13204| 344|Poke | 4.18| 2320| 13204| 344|Poke | 4.18| 632| 13204| 344|Poke | 4.18| 4264| 13204| 344|Poke | 4.18| 699| 13204| 344|Poke | 4.18| 1070| 13204| 344|Poke | 4.18| 3022| 13204| 344|Poke | 4.18| 1501| 13204| 344|Poke | 4.18| 808| 13204| 344|Poke | 4.18| 2793| 13204| 344|Poke | 4.18| 1660| 13204| 344|Poke | 4.18| 932| 13204| ... To be clear, there are ways to write this query without UNION that have both good performance and give the correct results, but they're very fiddly and harder to reason about that just writing the two comparatively simple queries and then mashing the results together.

gfody 6y ago

> Retrieving both employee and customer meal items When you're getting two separate sets of things in one query you should expect to use union. This isn't so much a performance optimization as just expressing the query properly. Imagine starting with just this weird SQL that gets both employee and customer meal items using only joins, and then trying to infer the original intent.

kroolik 6y ago

The author didn't mention the reason the query is slow. I dare to challenge that the non-optimal query is something that a person with advanced SQL skills would do. A seasoned engineer knows that by joining two tables, you are looking at, worst case, NxM comparisons. The problem is that you are joining the customers table with THREE other tables - the merged result of stores, employees and markouts. No matter how hard you try, you can't escape the fact that you are joining way more rows than just stores and customers. I would call it a layman issue SQL beginners learn the hard way. Advanced SQL users know the very basics of the db they are working with.

jiggawatts 6y ago
I don't even understand the thought process that would have led to the non-optimal query. It would simply never occur to me to use anything other than UNION ALL for this kind of scenario, because it is the only appropriate translation of the english-language business request into a SQL query.
- dwd 6y ago
  After working 20+ years in the industry I'm always surprised by the aversion (or lack of thought) to draw up a Venn diagram on a white board to visualise the relation, let alone then write it out using set notation.
- avereveard 6y ago
  Probably the result of some early attempt at either sorting or removing duplicates
- robertlagrant 6y ago
  It could be that the query was gradually modified. It's pretty simple to spot the union when the problem's being focused on, laid out nicely in a blog post, and the answer already given!
ncallaway 6y ago
> I would call it a layman issue SQL beginners learn the hard way. Advanced SQL users know the very basics of the db they are working with. So? Does every blog post have to be for expert users of the system? What's wrong with a blog post that explains mistakes that a beginner might make, and a method for mitigating it.

Black101 6y ago

speeding up sql queries isnt hard but it takes time and its worth it.

magicalhippo 6y ago

At work I ended up with something similar. A reporting query has about 20 sum()'ed columns, and found the easiest and by far fastest was just to have 20+ queries glued by UNION ALL, and aggregate the sums over that. It's heavy in it's raw form, but if you add some search terms to narrow things down the DB really cuts through. It's about 320 lines of SQL, so to make debugging a bit easier I added a dummy "identifier" column to each of the union'ed sub-queries, so first would have value 100, second 200 etc, so I could quickly identify which of the sub-queries produced a given row. Not counting in 1's allowed for easy insertion of additional sub-queries near relevant "identifiers".

danvk 6y ago

Why is it so important to express this as one SQL query? Why not just run the two simple queries? It feels as though two different types of things are being joined.

Daynil 6y ago
Depends on your goals. If you're a software engineer, it won't make a difference, just send two queries to the DB and combine the results before sending them off. If you're an analyst, you'll often get repeated ad-hoc requests. If you have to run 2 queries, then stitch them together, each time you are asked for a report, you're wasting tons of (really boring) time. Even if you ultimately create some code to run and stitch the results together for you, it's just faster and easier to write 1 query wherever possible. Just let the database do the work!
- Albert_Camus 6y ago
  > If you're a software engineer, it won't make a difference, just send two queries to the DB and combine the results before sending them off. This isn't true if you have certain use cases in your application, pagination being one of them. There's no simple way to implement pagination when you have two or more queries that return and unknown number of results and you're tasked with maintaining a consistent order across page changes. If you make a single query do all the work, it's easy to implement paging in any number of ways, the most performant being to filter by the last id the client saw. If your users aren't likely to paginate too far into the data, LIMIT and OFFSET will work fine as well.
drstewart 6y ago
You're falling into the trap of thinking like the developer looking at the implementation, not the end user. The goal is: >In order to audit inventory, the logistics team at the corporate headquarters requests a tool that can generate a report containing all meal_items that left a given store’s inventory on a particular day That's one thing. The end user doesn't see these as two separate requests just because that's how it's modeled in the database.

slt2021 6y ago

This is ABC of SQL: every SQL developer has to learn this lesson soon or later: Every additional JOIN you make creates a cartesian product of all previously joined records and the number of rows grows exponentially => you join tables A to B to C to D -> SQL engine has to create AxBxCxD number rows, each letter representing number of rows in that table) Of course specifying useful predicates that filter down dataset and having indexes on these columns helps avoid row explosion, but you can't have an index on each and every column, especially on OLTP database. in example #2 the author joined 7 tables (multiply row count of 7 tables and this gives you an indea of how many rows SQL engine has to churn through) - big O complexity of query is (AxBxCxDxExFxG) of course it will be SLOW. in example #4 he joins 4 tables and unions with 5 table join, so the big O complexity of query is AxBxCxD(1+E). same as in programming, there is Big O complexity in your SQL queries, so it helps to know O() compelxity of queries that you write TLDR: learn the SQL Big-O and stop compaining about the query planner, pls

tehlike 6y ago
Something that helps with exploding cartesian problem is using json_agg for the joined tables. IT's available in postgres, it works great.
meritt 6y ago
Your understanding is flawed, these aren’t cross joins.
jaredsohn 6y ago
I've found denormalization to be helpful for such performance issues in that it reduces the number of joins. It can also be useful for debugging if you want to keep track of values from other tables at the time that data was processed within the primary table.
- slt2021 6y ago
  denormalization aka memoization aka "dynamic programming of SQL" :-) - all three provide speed-up because they compute value once and reuse it later

deleted 6y ago

[deleted]

boulos 6y ago

Huh. Like other commenters, I’m surprised to see a multi part JOIN be the answer for “I want the results of Query A as well as the results of Query B”. If anything, I think many folks would say “whatever, make the two requests in parallel and zip them together in the application”. If not, WITH expressions to make the two independent queries and then a final SELECT to merge them however you prefer would be reasonable (whether that’s a join flavor or UNION/INTERSECT/etc.), but the super explosion JOIN just doesn’t cross my mind. I think there must be a different example that they tried to anonymize for this post, otherwise they don’t need the IDs (and probably want a sum of quantity). If the author is around, what was the more realistic example behind: “In order to audit inventory, the logistics team at the corporate headquarters requests a tool that can generate a report containing all meal_items that left a given store’s inventory on a particular day. This requires a query that includes items that were both sold to customers as well as recorded as employee markouts for the specified store on the specified day.”?

IgorPartola 6y ago
Where this fail (and I don’t see the article addressing this concern) is if you want to do pagination. Say you are retrieving page 17 of 8000. Will the UNION still be performant then? After all, it will require a stable sort order that may depend on one of the branches. You certainly can’t pagination without doing a whole lot of logic in your application if you use two separate queries. My typical solution to this is to do a query not employee IDs, another one of customer IDs and then do an IN clause. Or you could do an intermediate ID of some kind that will collapse your diamond branch into a single IN.
- catmanjan 6y ago
  In my experience this is the number one driver for infinite scroll, dropping paging simplifies a lot
  - sahila 6y ago
    Having infinite scroll doesn't avoid paging results from a query. Like the OP mentioned, if you have 8000 page * results/page records, you may only want to retrieve 50 records at a time to pass back to the client. In that case, you will still need a way to query for the next set / page. Infinite scroll is a UX implementation.

jjtheblunt 6y ago

I wonder if i'm the only one who sees the phrase "order(s) of magnitude" and expects a vague story. I don't mean to denigrate this article, just wonder if others have the same reaction.

mongol 6y ago

What is the best book to learn performant SQL querying? I recently needed to query a large database for exploratory purposes. The database has historical data about many years of sales transactions of a huge company. It is using partitioning keys. I know pretty well how to write queries to get what I want but given the size of the database I need to write them performantly. How can I learn that best?

sqldba 6y ago
SQL Performance Explained, hands-down https://sql-performance-explained.com/ https://sql-performance-explained.com/ Covers all the main DBMS, explained very simply from the bottom up, maintained constantly and has been read and recommended by people for years and years.

noisy_boy 6y ago

Everyone who the store is selling to is a customer; an employee is a customer too except the price might be zero. What if a field is added to customers table: employee_id (would be null by default as most customers would not be employee). Then the query can be run without any union and bypassing stores/employees/employee_markouts etc: SELECT meal_items.*, customer_id, customers.employee_id FROM customers INNER JOIN customer_orders ON customers.id = customer_orders.customer_id INNER JOIN customer_order_items ON customer_orders.id = customer_order_items.customer_order_id INNER JOIN meal_items ON customer_order_items.meal_item_id = meal_items.id Of course this means that customer_id will never be null but that kind of is the point. This can also be approached by adding a customer_id field to the employees table instead (in this example the no. of employees and customers are both 20000 but typically the no. of employees is less than the no. of customers so adding customer_id to employees table would be more typical - in which case the query would obviously change).

kroolik 6y ago
I think this is called denormalization, right? What you could try is to instead of modifying the domain for this specific use-case, create a read model instead (if you don't want to pollute your domain) . Sometimes its very easy to write data, but reading is a pain. For such cases, you can prepare read models that contains all the data necessary.
- noisy_boy 6y ago
  This is not specifically denormalization because I'm not combining/flattening two models - instead, I'm moving the special treatment for employees from a totally separate path to a property of the existing customer model without removing/merging the employee model. Regarding read optimizations, there are several ways, some of which are vendor specific e.g. PostgreSQL/Oracle allow materialized views which can make accessing this data simple (if often required though they have their own issues).
- deleted 6y ago
  [deleted]

gigatexal 6y ago

Would be cool to post the sqldump of the data somewhere so we could play with the queries and the database with the same data.

blunte 6y ago

Not speaking negatively about the author of this post, but union should have been the obvious tool to use in a scenario like this. SQL is very powerful and really not so complicated. Schema, index, and constraint design can be quite challenging, depending on the needs, but that's true whether using SQL or an ORM. The problem I see often is that people use ORMs and never learn SQL. Also often, one has to really work to understand how their ORM is handling certain situation to avoid problems like N+1 or cartesian products. In the end, it seems that rather than learning SQL, the developer ends up learning just as much or more about a specific ORM to get the same quality/performing results they would have gotten by using direct SQL.

02020202 6y ago

i use projections so simple query fuck the what.

zeroimpl 6y ago

I suspect query #2 would have performed better if they used a COALESCE instead of an OR condition. One of the most important optimization lessons you can learn is not to use OR for any join conditions. Eg instead of this: ON (customer_order_items.meal_item_id = meal_items.id OR employee_markouts.meal_item_id = meal_items.id ) Do this: ON meal_items.id = COALESCE( employee_markouts.meal_item_id, customer_order_items.meal_item_id ) The latter can optimize properly. Probably still slower than the UNION, but would be more in line with expected performance. It might be more useful too in certain cases, such as if building a view.

muad_kyrlach 6y ago

Am I the only one who rejects this data model? Customers belong to store and orders don't? I can see how a customer might have a primary store, but i would link the customer to the order and link the order to the store. Union's are awesome, but proper data modeling is better. :)

stremovsky 6y ago

Similar article of mine from 2013: https://cloudinvent.com/blog/optimize-mysql-count-query/ https://cloudinvent.com/blog/optimize-mysql-count-query/