Do you have any resources for learning cool advanced SQL like this? I've used CTEs in the past, just from reading about them in the documentation, but I feel like I'm only scratching the surface of what's possible out there.
Just read it. Seriously. Literally cover to cover, or nearly so. (Maybe simply scan the C-related sections if you're not into that, but do take note they exist. Similarly for some pl/ sections.) It might take you a few evenings, but you won't regret it. The Postgres docs is one of the best out there...
EDIT: When I wanted to learn about CTEs, for example, I conveniently had a large, ugly materialized view that needed refactoring, and they happened to fit the need perfectly. The previous version was, in places, five or six layers of subqueries deep, many of them repeated several times as they were reused. It hurt to read. Rewriting it to use CTEs made it about eight times faster, and the SQL script was a third the size of the original.
Still, its good to support Markus' effort and if you prefer having a PDF rather than going to the website, just buy it. It's like $15.
Use The Index, Luke is the free web-edition of SQL Performance Explained. If you like this site, consider getting the book. Also have a look at the shop for other cool stuff that supports this site.
Quoting from http://Use-The-Index-Luke.com/ (the main page):
Use The Index, Luke is the free web-edition of SQL Performance Explained. If you like this site, consider getting the book. Also have a look at the shop for other cool stuff that supports this site.