[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Is there a good discussion of optimizations?
From:       Michael Lewis <mlewis () entrata ! com>
Date:       2020-12-28 14:14:50
Message-ID: CAHOFxGpNO_yMNafWG_cLH6DZDBaOZ39u=4tA+yXTgF3F1RZ7QA () mail ! gmail ! com
[Download RAW message or body]

On Wed, Dec 23, 2020 at 6:56 PM Guyren Howe <guyren@gmail.com> wrote:

> I'd like to put together a good video and writeup about what the…
> philosophy behind relational databases is.
>
> 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… I
> see this particularly among analysts who for some reason often prefer SQL
> Server. I think this is down to afaict SQL Server having an abominable
> query optimizer.
>

I find temp tables quite helpful to get needed and consistent performance
when doing large data warehouse type queries on source data especially when
it isn't fully & properly normalized. Many row estimates being low because
of correlation with specified client_id and sometimes having 15-25 tables
involved in a report, has meant that temp tables (that are analyzed to
ensure statistics are present) have seemed the best tool for the job.
Perhaps that's all a hack though.

I look forward to when extended statistics may help with join planning and
building out a comprehensive warehouse that facilitates use of simpler
queries, but for now the "imperative straight-jacket" seems to help more
often than it hurts.

>

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr"><div dir="ltr">On Wed, Dec 23, 2020 at 6:56 PM Guyren \
Howe &lt;<a href="mailto:guyren@gmail.com" target="_blank">guyren@gmail.com</a>&gt; \
wrote:<br></div><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex">



<div>
<div name="messageBodySection">
<div dir="auto">I'd like to put together a good video and writeup about what the… \
philosophy behind relational databases is.<br> <br>
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… I see this particularly among analysts \
who for some reason often prefer SQL Server. I think this is down to afaict SQL \
Server having an abominable query \
optimizer.</div></div></div></blockquote><div><br></div><div>I find temp tables quite \
helpful to get needed and consistent performance when doing large data warehouse type \
queries on source data especially when it isn&#39;t fully &amp; properly normalized. \
Many row estimates being low because of correlation  with specified client_id and \
sometimes having 15-25 tables involved in a report, has meant that temp tables (that \
are analyzed to ensure statistics are present) have seemed the best tool for the job. \
Perhaps that&#39;s all a hack though.</div><div><br></div><div>I look forward to when \
extended statistics may help with join planning and building out a comprehensive \
warehouse that facilitates use of simpler queries, but for now the &quot;imperative \
straight-jacket&quot; seems to help more often than it hurts.</div></div></div><div \
class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div><div \
name="messageBodySection"> </div>
</div>

</blockquote></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]