[bob1029]

> the biggest players (MySQL, Postgres, Sqlite) have actually done a reasonably good job of making the 98% paths very good and 90% of the rest "good enough".

Exactly - Unless you are doing something really unusual (or wrong), the vendors of these engines have almost certainly encountered and optimized for some shape approximating your scenario.

If you want to get into some extreme ends of the practice, "ancient" engines like DB2 are some of the most capable. Many have been around longer than most developers today have been alive (myself included). That is a lot of optimization legacy to argue against. The halloween problem & iceberg meme comes quickly to mind. Why wouldn't you want to stick with something that has already dealt with all of that bullshit?

[throwawaymaths]

Eh, if you're at the point where you're doing stuff like "give me all records where a one-to-many has exactly two entries, and a different one to many has at least one record with property x" it gets VERY hard to do the correct performant SQL. I don't think that sort of query is too bizzare.