RE: Is there a good discussion of optimizations?

From: Guyren Howe <guyren@gmail.com>

>Most folks, in my experience, who use relational databases don’t really understand the basic theory or even more important the why - the philosophy - of what a relational database is and how to get the most out of them. I see a lot of folks trying to use SQL in an imperative manner - make this temp table, then update it some, then make this other temp table, etc...

An example of this is that we have a report we're trying to write that I'd

like to think can be done in SQL, but I can't think of a way to do it. Yet,

if I do the base query and pull the data back into my application, I can do

the last bit with 3 lines of Perl very easily. The problem here revolves

around comparing a row of data to the previous row to know whether the data

changed "significantly enough" to keep the new row.

Another example is doing running totals. A couple of years ago I would have

said SQL can't do that. Now I know about the OVER clause, something that I

would categorize as somewhat obscure, I can do it as needed.

As Michael Lewis pointed out, large dataset can also cause you to choose not

to use SQL in 1 big statemet for everything (i.e. advocating the use to temp

tables). In some ways, using a CTE is a type of temp table, or at least I

view it as such. That allows a person to solve a problem in bite-sized chunks.

I will agree that optimization can do it better at times, but the code also has

to be maintained as well – a balancing act.

I think your idea is a good one, but I do hope you present that SQL can't

solve everything ... else why do we have plpgsql. :) You’re correct though,

SQL isn’t used as much as it should be in many places.