4 ms·

> "oh you don't have to refer to the table name except when you do because of ambiguity issues" Maybe it's easier if you think of it like helpful syntactic sug

> "oh you don't have to refer to the table name except when you do because of ambiguity issues" Maybe it's easier if you think of it like helpful syntactic sugar? > I think there's a beautiful space for some SQL-like language that just operates a bit more like a general-purpose language in a more regular fashion. Bonus points for ones where you don't query tables but point at indexes or table scans and the like That sounds like imperative programming, which is fine for most things, but [generally] not RDBMS (or IaC, but that's a completely separate topic). You can't possibly know the state of a given table or index – the cardinality of columns, the relative grouping of tuples to one another, etc. While you can hint at index usage (natively with MySQL, via extension with Postgres), that's as close as the planner will let you get, because it knows these things better than you do. > resolving the "programmer writes query that is super non-performant because they assume an index is present when it's not" More frequently, I see "programmer writes query that is super non-performant because they haven't read the docs, and don't know the requirements for the planner to use the index." A few examples: * Given a table with columns foo, bar, baz, with an index on (foo, bar), a query with a predicate on `bar` alone is [generally] non-sargeable. Postgres can do this, but it's rare, and unlikely to perform as well as you'd want anyway. * Indices on columns are unlikely to be used for aggregations like GROUP BY, except in very specific circumstances for MySQL [0] (I'm not sure what limitations Postgres has on this). * Not knowing that a leading wildcard on a predicate (e.g. `WHERE user_name LIKE '%ara'`) will, except under two circumstances [1], skip using an index. > despite people intuitively understanding B-Trees or indexes. You say that, but the sheer number of devs I've talked to who are unaware that UUIDv4 is an abysmally bad choice WRT performance for indices – primary or secondary – says otherwise. [0]: https://dev.mysql.com/doc/refman/8.4/en/group-by-optimization.html https://dev.mysql.com/doc/refman/8.4/en/group-by-optimizatio... [1]: Postgres can create trigram indices, which can search with these, at the expense of the index being quite large. Both MySQL and Postgres can make use of the REVERSE() function to create a reverse index on the column, which can then be used in a query with the username also reversed.

rtpg 2y ago
In the universe in which I "know" what index to use (an assumption that can be contested!), you saying "well silly you, the planner is too stupid to figure this out" is not a great defense of the system! My serious belief is that all the SQL variants are generally great, but I just want this to be incremented with some lower-level language that I can be more explicit with, from time to time. If only because sometimes there are operational needs. The fact that the best we get with this is planner _hints_ is still to this day surprising to me. Hints! I am in control of the machine, why shouldn't it just listen to me! (and to stop the "but random data analyst could break thing", this is why we have invented permission systems)
- sgarland 2y ago
  > you saying "well silly you, the planner is too stupid to figure this out" is not a great defense of the system! I don’t think that’s what I said. At least, it wasn’t what I meant. The planner needs accurate stats on the tables to produce an optimal output. So would you, as a human, if you were writing a query to be executed precisely as written. It also needs to know information about the system it’s operating in – the amount of RAM it can expect to use being the most important one, but also things like the relative performance of a random disk seek vs. sequential. The planner also has a huge leg up on most people, in that it is aware of all of the kinds of joins and lookups it can do. Off the top of my head, Postgres can scan in the following ways: sequential scan, parallel sequential scan, index scan, parallel index scan, index-only scan, and bitmap scan. For joins, it can use: nested loop, merge, hash. Do you know when all of those are ideal? What about when you have a query joining multiple tables, keeping in mind that the intermediate contents of a table that has been joined once may look nothing like what it started as?
  - Izkata 2y ago
    > Off the top of my head, Postgres can scan in the following ways: sequential scan, parallel sequential scan, index scan, parallel index scan, index-only scan, and bitmap scan. I don't think that last one is a thing. There is both "bitmap heap scan" and "bitmap index scan" though.
    - sgarland 2y ago
      It’s the generalized parent of both of those (since you will not see one without the other). You’re technically correct, but I’m also not the only one [0] who uses this term. [0]: https://www.cybertec-postgresql.com/en/postgresql-indexing-index-scan-vs-bitmap-scan-vs-sequential-scan-basics/ https://www.cybertec-postgresql.com/en/postgresql-indexing-i...
Izkata 2y ago
> * Given a table with columns foo, bar, baz, with an index on (foo, bar), a query with a predicate on `bar` alone is [generally] non-sargeable. Postgres can do this, but it's rare, and unlikely to perform as well as you'd want anyway. The one I see many times more often than this is having one index on (foo) and one index on (bar), then querying on both foo and bar, and assuming the database uses both indexes for best performance. It can be done, even by ancient mysql, but it's most likely going to be a lot slower than expected so they're heavily biased against doing it and will most likely just choose one of the two indexes.

rtpg 2y ago

In the universe in which I "know" what index to use (an assumption that can be contested!), you saying "well silly you, the planner is too stupid to figure this out" is not a great defense of the system! My serious belief is that all the SQL variants are generally great, but I just want this to be incremented with some lower-level language that I can be more explicit with, from time to time. If only because sometimes there are operational needs. The fact that the best we get with this is planner _hints_ is still to this day surprising to me. Hints! I am in control of the machine, why shouldn't it just listen to me! (and to stop the "but random data analyst could break thing", this is why we have invented permission systems)

sgarland 2y ago
> you saying "well silly you, the planner is too stupid to figure this out" is not a great defense of the system! I don’t think that’s what I said. At least, it wasn’t what I meant. The planner needs accurate stats on the tables to produce an optimal output. So would you, as a human, if you were writing a query to be executed precisely as written. It also needs to know information about the system it’s operating in – the amount of RAM it can expect to use being the most important one, but also things like the relative performance of a random disk seek vs. sequential. The planner also has a huge leg up on most people, in that it is aware of all of the kinds of joins and lookups it can do. Off the top of my head, Postgres can scan in the following ways: sequential scan, parallel sequential scan, index scan, parallel index scan, index-only scan, and bitmap scan. For joins, it can use: nested loop, merge, hash. Do you know when all of those are ideal? What about when you have a query joining multiple tables, keeping in mind that the intermediate contents of a table that has been joined once may look nothing like what it started as?
- Izkata 2y ago
  > Off the top of my head, Postgres can scan in the following ways: sequential scan, parallel sequential scan, index scan, parallel index scan, index-only scan, and bitmap scan. I don't think that last one is a thing. There is both "bitmap heap scan" and "bitmap index scan" though.
  - sgarland 2y ago
    It’s the generalized parent of both of those (since you will not see one without the other). You’re technically correct, but I’m also not the only one [0] who uses this term. [0]: https://www.cybertec-postgresql.com/en/postgresql-indexing-index-scan-vs-bitmap-scan-vs-sequential-scan-basics/ https://www.cybertec-postgresql.com/en/postgresql-indexing-i...

Izkata 2y ago

> * Given a table with columns foo, bar, baz, with an index on (foo, bar), a query with a predicate on `bar` alone is [generally] non-sargeable. Postgres can do this, but it's rare, and unlikely to perform as well as you'd want anyway. The one I see many times more often than this is having one index on (foo) and one index on (bar), then querying on both foo and bar, and assuming the database uses both indexes for best performance. It can be done, even by ancient mysql, but it's most likely going to be a lot slower than expected so they're heavily biased against doing it and will most likely just choose one of the two indexes.