10 ms·
The many faces of DISTINCT in Postgres (2017)
- t00 3y ago> We can immediately see that everyone in the support department are making the same salary. Bonnie Robertson is a thriving 10X support high earner
- deleted 3y ago[deleted]
- codeflo 3y ago> A classic job interview question is finding the employee with the highest salary in each department. Here’s a cheat code, in case you need to write a database query and don’t remember all the fancy join tricks: Problems like this often have a very straightforward solution with subselects. In this case, the main select gets the departments, and a subselect (with limit 1) fetches the top employee for each department. That’s a very natural, compositional way of thinking. Granted, it’s not the most optimized way to do it, but more often then not, the resulting query plan is perfectly fine.
- andy800 3y agoSorry, how would you write this query just with LIMIT 1 and not window functions or MAX or a join?
- antifa 3y ago> Granted, it’s not the most optimized way to do it I've discovered a small number of queries actual do become faster when switching a column to a subselect.
- paol 3y ago> It’s not the most optimized way to do it, but more often then not, the query plan is perfectly fine. Depending on the details, the query plan might be exactly the same. I remember once (in SQL Server 2000, so long time ago) writing the same query in 3 completely different ways, only to see the DB generate the same plan for each one. It would surprise most people how sophisticated the query transformations databases can do.
- deleted 3y ago[deleted]
- dunham 3y agoI had one case, with the same SQL server, where rewriting to a join made the query much faster. That was a data changing statement though, and I think the semantics demanded it rerun the subselect. More recently, I've found that mysql sometimes behaves poorly with subselects, so I tend to avoid them out of habit.
- emptyfile 3y ago[dead]
- foldr 3y agoAlso easy to do this with a lateral join (and a limit 1 in the subquery).
- giraffe_lady 3y agoIf you can do a lateral join off the top of your head this isn't the kind of question you need to have a strategy for anyway.
- foldr 3y agoI think everyone tends to learn their own subset of SQL. I was unaware of some of the more 'obvious' solutions in the article (e.g. I had only a very vague idea of how window functions work), but I use lateral joins all the time. The nice thing about lateral joins is that they're very easy to understand conceptually once you get over the weird syntax.
- andrenotgiant 3y agoHow would you explain them to someone who knows the syntax but still finds them counterintuitive?
- foldr 3y agoI just think of it as performing a subquery for every row of the main query.
- dagss 3y agoTo me a lateral join is the simplest construct you could have, like a for loop in a programming language. For each row in my query so far, do this other thing. Since I learned them, everything went from being a puzzle on its own, "I know what I want to do but how to express it in SQL" -- to simply being writing things out naturally..
- magicalhippo 3y agoTIL lateral joins exist, and I struggled to understand what exactly they did because for some reasons all the blog posts and whatnot were so convoluted. Then I found this[1] SO answer, which lit my bulb. We're using such subqueries a lot, often for many columns in the same child table, and we're moving to MSSQL so will definitely change to using lateral join (or cross apply as MSSQL calls it). [1]: https://stackoverflow.com/a/28550962 https://stackoverflow.com/a/28550962
- paulddraper 3y ago> Granted, it’s not the most optimized way to do it, but more often then not, the resulting query plan is perfectly fine. It usually is the exactly same. (MySQL has a potato for a query optimizer tho.) The biggest case it is suboptimal is when you need to produce multiple fields from the sub-select. Because then you need multiple sub-selects.
- neallindsay 3y agoThe author mentions having to get over the lack of upsert when moving from Oracle. But readers might like to know this isn’t a problem anymore since Postgres got “INSERT … ON CONFLICT UPDATE …”.
- paulryanrogers 3y agoAnd MERGE
- mattashii 3y agoYes, that's been a thing since 9.5, which was released in early 2016.
- cdogl 3y agoI am a huge Postgres fan, but “ON CONFLICT UPDATE” does not always cut the mustard. You can’t target conflicts against multiple constraints, so if there are multiple constraints that could cause conflicts you’re stuck either figuring out whether you can drop a constraint or doing something clever in your client. This is often a smell, but it’s a little inflexible.
- richbell 3y agoIn addition to this, `ON CONFLICT` is still susceptible to race-conditions if you have a highly concurrent application. This may be obvious to some, however, a lot of people expect upsert to be atomic and get bit by this. I also dislike that `ON CONFLICT ... DO NOTHING` increments numerical primary keys. I understand why it happens but it seems counter-intuitive given the name "DO NOTHING". (And, yes, relying on the values of primary keys to never change is an anti-pattern. However, if you have a table of 10 values and the primary keys have massive gaps like 1, 500_211, 2_521_241, 15_631_121, etc., it feels weird nonetheless.)
- jack_squat 3y agoFrom the Postgres docs, ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. This is also known as UPSERT — “UPDATE or INSERT”. https://www.postgresql.org/docs/current/sql-insert.html https://www.postgresql.org/docs/current/sql-insert.html What are you referring to?
- cpursley 3y agoI only learned about the ranked query approach last week thanks to ChatGTP. Helped me solve a hairy query that rolled up activity events grouped by time periods. Before that I was struggling with distinct (and it was slow). I’ve avoided ChatGTP until recently and at least for SQL refactoring, it’s great. The interesting part is the ranked example ChatCTP gave me was almost identical to the one in this post. I wonder if they’re (ChatGTP) is training up on technical blog posts.
- swader999 3y agoI throw it lines of logs of sql from Paper trail and ask it to extract the SQL statements. Then I give it the analyse explain output. Sometimes ChatGPT will give me great advice on optimizing.
- cpursley 3y agoDang, that's a good idea (as long as not production data). It seems like eventually database automation is going be self-optimizing (more than the query planner already is).
- maweki 3y ago"(easy CS students, I know it's not normalized…)" Sure it is. As long as you're not storing any other information on the department in the employee table.
- thehappypm 3y agoAuthor means it should be a department_id with a table mapping that to a name to be normalized like a student is taught
- daigoba66 3y agoAnd as long as you never rename the departments.
- maweki 3y agoThat has no bearing on normalization.
- Izkata 3y agoIt's an almost verbatim example of getting to 1NF, the first and most basic normalization. The value (department name) is repeating and should be extracted and given its own ID.
- maweki 3y ago> The value (department name) is repeating and should be extracted Then the id would be repeating. Furthermore, the department name would make a fine primary or alternate key for the new relation you're proposing. Also, that's not what 1NF is. 1NF means there should be no table-valued attributes. And neither is any column list-valued nor does any subset of columns form a subtable. The other normal forms talk about functional dependencies and there aren't any. The only possible violation of 1NF could be not splitting the name in given name and family name. Other than that, the table is normalized.
- 3y ago
- xupybd 3y agoMSSQL does have something closer to array agg. https://www.mssqltips.com/sqlservertip/5542/using-for-xml-path-and-stringagg-to-denormalize-sql-server-data/ https://www.mssqltips.com/sqlservertip/5542/using-for-xml-pa...
- veddan 3y agoThere's also JSON_ARRAYAGG. https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_json-arrayagg https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions....
- pophenat 3y agoMicrosoft SQL Server now also has IS [NOT] DISTINCT FROM. https://learn.microsoft.com/en-us/sql/t-sql/queries/is-distinct-from-transact-sql?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/t-sql/queries/is-disti...
- justinclift 3y ago2017
- zzzeek 3y agoI've never gotten into DISTINCT ON and it's always confused me, I'd rather see the query with MAX and GROUP BY if I'm looking for "the highest X in groups of Y". For the same reason I don't prefer RSA-in-three-lines.
- kubota 3y agoOracle listaggs do support distinct, since 19c.
- ak39 3y agoI’ve never truly used DISTINCT and felt comfortable for using it. Always felt using it revealed a design smell in my query. (Been doing SQL for 30 years and still!)
- netcraft 3y agoyes, thats exactly the way I feel. It might be the right answer, but oftentimes its a smell. Theres very few cases where you shouldnt be doing a group by with aggregates instead. (window functions count here)