The key idea here seems to be that if you’re grouping on a column on a related table you can do your main aggregation by grouping on the foreign key id on the primary table and use that as a proxy for the data on the related table that you’re actually grouping by.
In the examples given, it’s much faster, but is that mostly due to the missing indexes? I’d have thought that an optimal approach in the colour example would be to look at the product.color_id index, get the counts directly from there and you’re pretty much done.
I have a feeling that Postgres doesn’t make that optimisation (I’ve looked before, but it was older Postgres). And I guess depending on the aggregation maybe it’s not useful in the general case. Maybe in this new world it _can_ make that optimisation?
Anyway, as ever, pg just getting faster is always good.
> In the examples given, it’s much faster, but is that mostly due to the missing indexes? I’d have thought that an optimal approach in the colour example would be to look at the product.color_id index, get the counts directly from there and you’re pretty much done.
So I tried to test this (my intuition being that indexes wouldn't change much, at best you could just do an index scan instead of a seq scan), and I couldn't understand the plans I was getting, until I realized that the query in the blog post has a small error:
> AND c1.category_id = c1.category_id
should really be
> AND p.category_id = c1.category_id
otherwise we're doing a cross-product on the category. Probably doesn't really change much, but still a bit of an oopsie. Anyway, even with the right join condition an index only reduces execution time by about 20% in my tests, through an index scan.
That is part of the key idea, yes. It's more elaborate, because it can split the aggregate - it can do part of it before the join, and finalize it after the join. Similarly to what we do for parallel queries.
As for indexes, it can help, but not in this particular example - the "code" tables are tiny, and the planner adds Memoize nodes anyway, so it acts like an ad hoc index.
Indexes are more of a complementary improvement, not an alternative to this optimization (i.e. neither makes the other unnecessary). FWIW in this case the indexes won't help very much - if you use more data in the code tables, it'll use a hash join, not nested loop / merge join.
That doesn't mean we couldn't do better with indexes, there probably are smart execution strategies for certain types of queries. But indexes also come with quite a bit of overhead (even in read-only workloads).
1. Index-only scans on t_product.{category,color} indices, summing each value
2. Lookup the names of those values in their parent tables, generate output rows
If so, I suspect there are two reasons why it might not do that:
Given the relatively small size of the t_product table (23 bytes overhead + 1 byte padding + int4 + int4 + 16 bytes text + [I think] 1 byte varlena = 49 bytes/row), it will be fairly well bin-packed into pages on the heap, consuming roughly 170 pages, assuming 8 KiB default, and default fillfactor of 100%). That trivially fits into a single segment file on-disk, and is a very easy sequential scan.
If it does a sequential scan on the heap, it doesn’t have to check the Visibility Map, because it already has that information in the heap itself, which avoids a second (albeit small) lookup.
Happy for someone who knows more about Postgres to correct me if I’m wrong, though!
> In the examples given, it’s much faster, but is that mostly due to the missing indexes?
You're saying “the missing indexes” as if you could add indexes for every join you're ever doing and that this would be faster than a hash join. For many systems, that's not feasible nor very performant; and depending on selectivity, hash join would often be better than an index lookup anyway.
The biggest win from early aggregation is that you can reduce the number of rows significantly before you go join in other things (which would be a win even in nested-loop index lookup joins; smaller joins are nearly always better along every axis).
Not decorators (or at least not last time I looked) but we use fast-check.
Was already familiar with and using Hypothesis in Python so went in search of something with similar nice ergonomics. Am happy with fast-check in that regard.
In addition, do people do so in mesh format? Seems expensive to do so for all of your machines, more often the topology I see is a relay/subnet advertisement based architecture that handles L3 and some other system handles L6/L7
I don't really think asbestos is something that needs balance to the conversation. It's like radioactive material, you will most likely not even know that you've had too much exposure until your health is already permanently affected. The illness may manifest a considerable time later so you might not even know what it was. It's very easy to unknowingly be exposed when e.g. renovating a house or other similar arrangements because there is no easy visual way to identify materials containing asbestos.
It absolutely does need balance. Many, if not most older pre 1980 houses in (Norway where I live) have some form of Asbestos in some form, e.g. Eternit, window putty, jointing putty, AIB around fireplaces, or textured paint. Usually these materials are fine if left in place, and it is tricky to avoid these materials when looking for housing since older homes make up a large fraction of our housing stock, especially in my kommune up north.
My own house probably contains some Asbestos, but getting an asbestos survey is very expensive, prohibitively so for people earning the average salary. Not to mention if asbestos is found, it is a further expense to get it removed and cleaned up. The best I can do is send a sample in for testing if I find something risky while renovating myself. Most contractors up here do no care at all if something looks like it may be ACM.
The best thing we can do for people is to provide balanced guidance on where asbestos may be and how much risk does it pose. AIB like Asbestolux is way more dangerous than Eternit is and depending on the location Eternit can remain in place.
Unfortunately if we were to take a zero tolerance approach it would cause more harm than good. How do people pay for remediation? do we all abandon our homes? what happens to the farmers who cannot move? I have no perfect answer here. Asbestos is a hazard no question, but what can we do other than common sense and balance?
> Usually these materials are fine if left in place
Maybe in Norway you need to tell people to chill over it. But the current attitude to asbestos in DIY spaces in the US/UK is far away from that, and a lot closer to “yea go on just tear it out yourself, you’ll be fine if you put on an n95 and spray some water first”.
I think the dominant attitude in the US is to stick your head in the sand. Tons of older homes have asbestos yet almost zero of them report it in their listing or their disclosure report. If you test it and find it you have to report it. If the buyer tests it and finds it, the seller doesn't want to know because then they'd have to report it. Somehow knowing the house has asbestos lowers it's value significantly more than willfully not knowing + almost certainly having it anyways.
Happened to me. Bought a house with wood floors in the basement. We had some flooding which ruined the wood and when we ripped it out to replace, turns out the wood floors were installed over the original asbestos tiles. From what I can tell, the asbestos tiles themselves were of no particular danger to us, but once they got wet and started cracking they had to be removed which cost an additional couple thousand dollars on top of replacing he floors.
Yes because proper asbestos removal is very costly and people don't want to deal with it, your house is going to look like the government coming for ET. Very tempting for a singular homeowner to just read up and rent some ventilation equipment and rip it out themselves on the down low even if dangers are very real. There are some old mental asylums in my hometown, beautiful stone buildings on massive property, they have been sitting vacant and decaying for years because they are full of asbestos and no one wants to deal with it.
The problem with not taking a "zero tolerance" approach is that the problem isn't going to go away. As you say, the asbestos including materials are often best left untouched rather than being removed by a DIYer which may well release the fibres into the air. However, that's just kicking the can down the road as at some point the house will need renovating or rebuilding and that's when the asbestos fibres will be released.
As always, it ends up cheaper to just chuck dangerous materials into the wider environment rather than dealing with them in a responsible manner. It's a shame that we can't retroactively penalise the builders that used so much asbestos.
In Germany, regulations around asbestos are quite strict: You're not allowed to "seal it in" by e.g. putting floor leveling compounds on top of old asbestos-containing flooring. If there's asbestos found during renovations, it needs to be removed professionally.
When we re-did our kitchen we found asbestos-containing glue under a new-ish layer of tiles one of the previous owners of the apartment just laid on top. I wish regulations would already have been stricter back then (and that they would have been followed - another story...) as this surprise find caused massive delays to the construction and forced us to temporarily move out during the removal and decontamination.
One of the best ways to make DIY-ing in buildings built before 1994 (when asbestos was banned for construction in Germany) safe has been to buy a H-filtration class shop vac. It can filter out asbestos fibers and many other fine dust particles that aren't healthy to inhale and was barely more expensive than a comparably good vac.
You can’t punish retroactively, Lex retro non agit.
It’s the lawmakers that create the environment we all work inside of to maximize our profits. There’s probably at least 3 as dangerous as asbestos materials used in current day and we are yet to find out.
Zero tolerance means paralysis, it’s a naive ideal. Look at the recent medical technologies where zero tolerance for unwanted side effects has to be balanced with benefits.
With asbestos, it's not so much a trade-off as it will continue to be a possible hazard unless it's disposed of correctly. I can relate to a homeowner not wanting to pay for testing/removing asbestos ceiling tiles etc. but if they don't then any disruption to those tiles can release potentially lethal fibres into the air (asbestos doesn't have a "safe" dose, so it's not like it can be rationalised with "I only made a small hole in that tile").
Comparison with medical technologies isn't particularly valid as people can choose whether to have that treatment or not, but people can't easily choose whether their house was built with asbestos or not. If a house was already built, then the potential danger is already there and we can either deal with it sensibly or not care about the deadly consequences of releasing it into the environment.
I don't see the logic of not punishing retroactively as companies may have made a lot of profit and then pay nothing towards the clean-up costs - privatise the profit and socialise the costs. The homeowners/tenants are effectively being punished retroactively when they may suspect/discover that their house contains asbestos, so why should the builders (if they are still around) not have to pay?
Maybe there should be extra taxes imposed on industries with a history of environmental abuse to reclaim some of the costs to society.
The EPA’s own documentation states that Asbestos is primarily an occupational hazard. If the fibers are not embedded in a friable material you will not get any significant exposure. Occupational hazards are those encountered routinely by someone engaging in an occupation. So harvey the homeowner isn’t at much increased risk if he removes asbestos himself as long as he takes precautions and cleans up afterward. It’s a fiber and a dust not some magic material that soaks in through your skin. And obviously don’t start a business removing asbestos for people without doing the licensing.
Fiberglass batts are really bad to handle too, and same with gypsum dust and saw dust from cutting manufactured stone countertops. If you do anything indoors you should definitely wear a respirator and full sleeves.
> It’s a fiber and a dust not some magic material that soaks in through your skin
I feel you're being a bit flippant with the known danger of disturbing asbestos containing materials. The dust and fibres are typically too fine to be controlled by a household vacuum cleaner and will require specialist handling to minimise the risk. Yes, the dust isn't absorbed through the skin, but instead is breathed in and enters the lungs where it causes problems.
You're right about using respirators/masks when dealing with dust, but special care needs to be taken with asbestos dust and not all dust masks will protect your lungs from the dust/fibres.
Whether or not specific regulations exist for a particular danger, if a company or individual can be found to be aware of the negative consequences of their use of certain compounds and they don't publicly document those dangers they can (in the US anyway) and should absolutely be held legally accountable for failure to warn.
History is rife with companies/industries who were well aware of the dangers they were creating to people for decades while actively suppressing the evidence of such that they themselves discovered.
Which companies this applies to for Asbestos in particular, I couldn't tell you, but it would be shocking if there weren't lots of bad actors who knew they were bad actors considering there has been research on the dangers of Asbestos since 1927 -- nearly a full century ago.
> retroactively penalise the builders that used so much asbestos
It is not the builders fault, they did not know, but the manufacturers of ACM did for decades! and they were penalized. Most were forced to set up trusts to cover certain expenses in the US, but I am not sure what their scope is.
Yes, that is why when you test positive for asbestos you add a little "a" sticker to the material to notify anyone in the future.
I think you are missing the point. Many people like myself want to take care of it, and would hire a proper crew to take care of it, but we are not wealthy enough to just call in a crew without some financial planning. This is not just an annoying expense, quite a bit more than that.
Unlike radiation, there's no safe asbestos exposure, if you're really unlucky a single strand can screw you up. On the other hand, of all people having worked at asbestos facilities with early 20th century approach to PPE only 20% developed mesothelioma and 10% died from it.
Thinking before swinging your drill will get most people safe enough not to worry about it.
I don't really buy the comparison. If you're really unlucky, you can get cancer from a "safe dose" of radiation.
Low exposures of both things are statistically less likely to hurt you than large doses. We pick a line to call "safe", but completely safety in either case is not guaranteed.
There is a natural level of radioactivity which the body is used to compensate. Small additional doses of radioactivity can therefor be neglected. This is not true for stuff like abestos.
Question for people who have gone through the journey of being tech founders that have grown a company. At what size of org / engineering team would you expect the founder to not code at all anymore?
We sync content to MS hosted Sharepoint using rsync. When the file arrives, they change the internal metadata inside the file, which changes the checksum, which causes rsync to think the content is different and needs syncing again.
Amen. I put my dishwasher in myself so I get to curse myself for that hacking.
Worst was sourcing the parts though. Getting the thing out, effectively getting it up on blocks to run it and see the issue was hard work. Getting the specific totally non-standard o-ring size out of the manufacturer was impossible. In the end I resorted to siliconing but I just cannot dump something like that over a 5c part.
https://github.com/anthropics/claude-code/blob/main/plugins/...
reply