3 ms·
I feel the best abstraction for hints would be to declare on tables how large you expect them to be -- and even throw errors if query plans with a good scaling
by dagss 3y ago
I feel the best abstraction for hints would be to declare on tables how large you expect them to be -- and even throw errors if query plans with a good scaling cannot be found.
Say I could declare "assume this table will grow very large", "assume this table will be a small enum table".
And then it would use that information instead of actual table size to guide planning AND throw an error for any query doing a full table scan on a declared-to-be-large table -- so that missing indices can be detected instantly, not after running in prod for some days/weeks.
Google Data Store has this property and it is a joy to work with for a backend developer.
What I am usually after is NOT the fastest plan, but the most consistent and robust plan across test and prod environments.
- Semaphor 3y agoOh, that sounds really cool. I like declaring expected size, but "throw on certain behaviors" would be something I’d love in MS SQL.
- barrkel 3y agoTable row count is a small part of it, what matters is cardinality and fanout from joins after predicates have been pushed down as far as they can. (Assuming there are sane indexes.) If the database can't see that your predicates will restrict the set of rows at a certain point in the join graph, it is likely to decide to join too much too early with huge table scans. Bad join order and join strategy is at the heart of most bad plans once you already have indexes in place that cover the expected joins and lookups.