RE: Is there a good discussion of optimizations?

On Jan 7, 2021, 13:07 -0800, Kevin Brannen <KBrannen(at)efji(dot)com>, wrote:
> From: Guyren Howe <guyren(at)gmail(dot)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...

Actually, I’m mostly going to talk about the relational model, rather than SQL. Our industry seems to always settle for third-best, and SQL is the worst of all the examples of this. The world desperately needs a good relational database based on a better query language — datalog, for example.

I put up with SQL so I can use the relational model, and I think that understanding SQL has to start with that.

Anyhow.
> 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.

Actually, Window functions might be “advanced”, but are certainly not obscure. Your example sounds like it’s trivially solved with LAG().
> 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.

This appears to be good advice with SQL Server, which I’m coming to learn has a fairly poor query optimizer. But I would have thought Postgres’s optimizer would usually use a temporary table where appropriate.

Curious to hear if that’s wrong.
> 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.

An important consideration will be when relational is inappropriate. My biggest goal, though, is to get folks to understand how much relations *can* do — far too many devs in my experience don’t use the power of SQL because they don’t understand it.

Thanks for taking the time to give me this feedback.