> Instead of smartly reasoning about the data, it is all to easy to just "JOIN ALL THE THINGS WITH MEGA TEMP TABLES!".
This is so common, I'd love for the popular databases to add table flags that prevent it by accident. Letting me configure "this table most not full scan or file sort implicitly" would get rid of half the incident callouts I've been involved in. You could always override it in the query where needed.
At best (if your users aren’t allowed to write SQL), that would change your “it is slow” calls to “it doesn’t work”.
At worst, I fear your users would learn to override it by default, as just one other part of the magic incantation needed to please the SQL gods.
I think it would be better to have the planner send out emails “this query has to use a full scan” or, “this query is on the brink of changing strategy compared to earlier runs”
> that would change your “it is slow” calls to “it doesn’t work”.
But that's exactly what I'm asking for. "It's slow" means that it will work until the breaking point and then wake me up. "It doesn't work" with the right reporting allows me to teach someone about indexing during office hours.
I found it not very useful. Sometimes you will query without indexes. Sometimes it just doesn't matter. Sometimes it's a table with 2 rows and you don't want an index.
In practice I don't want to know when it happens. I want an error to be raised instead so the database doesn't suddenly die.
This is so common, I'd love for the popular databases to add table flags that prevent it by accident. Letting me configure "this table most not full scan or file sort implicitly" would get rid of half the incident callouts I've been involved in. You could always override it in the query where needed.