4 ms·

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 li

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.

Vivtek 12y ago
Second that request. What can I read to go beyond my SELECT * from t order by c-level knowledge?
- AlisdairO 12y ago
  Apologies for the self-plug, but you might find some stuff in the later-stage Aggregates and Recursive exercises on http://pgexercises.com http://pgexercises.com . There's a bunch of stuff on CTEs and window functions in there.
- jklowden 12y ago
  I like Date's SQL and Relational Theory (http://shop.oreilly.com/product/0636920022879.do http://shop.oreilly.com/product/0636920022879.do). It won't tell you about the latest changes to the standard, but it will help you get a handle on the relational model, which is the key to using SQL intelligently.
ddebernardy 12y ago
http://www.postgresql.org/docs/ http://www.postgresql.org/docs/ 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...
mahmud 12y ago
The author of the slides, Markus Winand, wrote the book "SQL Performance Explained".
- kevinbowman 12y ago
  I was in his Modern SQL talk at FOSDEM on Saturday, he clearly knows his stuff. His book is definitely on my list.
  - fatalmind 12y ago
    X-comment so you get it too: Quoting from http://Use-The-Index-Luke.com/ 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. http://use-the-index-luke.com/shop http://use-the-index-luke.com/shop
- xtrumanx 12y ago
  The book appears to me to be the contents of use-the-index-luke.com. My friends impulsed bought it when I told him I was planning on buying it after confirming that it had more content than the website but after skimming the table of contents it doesn't appear to have anything more than the website. 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.
  - fatalmind 12y ago
    Quoting from http://Use-The-Index-Luke.com/ 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. http://use-the-index-luke.com/shop http://use-the-index-luke.com/shop
rosser 12y ago
Other than The Fine Manual, linked by a sibling comment, not really. Everything I've learned, I've just picked up along the way. 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.

Vivtek 12y ago

Second that request. What can I read to go beyond my SELECT * from t order by c-level knowledge?

AlisdairO 12y ago
Apologies for the self-plug, but you might find some stuff in the later-stage Aggregates and Recursive exercises on http://pgexercises.com http://pgexercises.com . There's a bunch of stuff on CTEs and window functions in there.
jklowden 12y ago
I like Date's SQL and Relational Theory (http://shop.oreilly.com/product/0636920022879.do http://shop.oreilly.com/product/0636920022879.do). It won't tell you about the latest changes to the standard, but it will help you get a handle on the relational model, which is the key to using SQL intelligently.

ddebernardy 12y ago

http://www.postgresql.org/docs/ http://www.postgresql.org/docs/ 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...

mahmud 12y ago

The author of the slides, Markus Winand, wrote the book "SQL Performance Explained".

kevinbowman 12y ago
I was in his Modern SQL talk at FOSDEM on Saturday, he clearly knows his stuff. His book is definitely on my list.
- fatalmind 12y ago
  X-comment so you get it too: Quoting from http://Use-The-Index-Luke.com/ 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. http://use-the-index-luke.com/shop http://use-the-index-luke.com/shop
xtrumanx 12y ago
The book appears to me to be the contents of use-the-index-luke.com. My friends impulsed bought it when I told him I was planning on buying it after confirming that it had more content than the website but after skimming the table of contents it doesn't appear to have anything more than the website. 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.
- fatalmind 12y ago
  Quoting from http://Use-The-Index-Luke.com/ 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. http://use-the-index-luke.com/shop http://use-the-index-luke.com/shop

rosser 12y ago

Other than The Fine Manual, linked by a sibling comment, not really. Everything I've learned, I've just picked up along the way. 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.