Aw, just when it was getting good, it cuts off for the next episode, where the table has an index. This episode only covers what happens with no index.
It gets really interesting when the query involves multiple tables and indices, and the query optimizer has a choice of strategies. You don't really get the benefits of a full SQL query engine until you ask it to do something hard.
I really enjoyed that. The Postgres docs have a really great overview of this stuff too. I started reading it one evening and ended up going through it all because it's so interesting and presented in a really approachable way.
This article superbly bridges the (usually very large) gap between Database-Theory and Databases-in-Reality.
It'd also be interesting to learn about how MySQL and Postgres differ in terms of how they process queries internally. I'm sure there'd be interesting tradeoffs all over the place.
If someone were to do a C-based API server, does Postgres have an API to interface with it at the PLAN or EXECUTE level, to save from long query processing? Say, a query with dozens of joins and columns, and you want to shave off 500 microseconds? Libpq doesn't seem to have something like that.
It gets really interesting when the query involves multiple tables and indices, and the query optimizer has a choice of strategies. You don't really get the benefits of a full SQL query engine until you ask it to do something hard.