
My Favorite PostgreSQL Queries and Why They Matter - grzm
https://severalnines.com/blog/my-favorite-postgresql-queries-and-why-they-matter
======
da_chicken
> 6\. UPDATE multiple rows with a CASE expression

Oh, man, as a seasoned DBA/Data Analyst, don't do this unless you have a
really good reason to. This is premature optimization of the kind you want to
avoid.

Yes, it's really neat to update everything in a single statement, and in
_some_ situations it can perform significantly better, but CASE expressions in
an UPDATE statement quickly get complicated to map out in your head. It gets
extremely difficult extremely quickly to tell where you have an error, and
it's very, very easy to make a very costly mistake.

If you really need an atomic change you can just do this:

    
    
      BEGIN;
      UPDATE reward_members SET member_status = 'gold_group' WHERE member_status = 'gold';
      UPDATE reward_members SET member_status = 'bronze_group' WHERE member_status = 'bronze';
      UPDATE reward_members SET member_status = 'platinum_group' WHERE member_status = 'platinum';
      UPDATE reward_members SET member_status = 'silver_group' WHERE member_status = 'silver';
      COMMIT;
    

In general, however, make your queries difficult for the server and easy for
you, because _you_ make a ton more mistakes than the server ever will. Let the
query planner and optimizer do the work. If performance becomes a problem on
this query, you can fix it later when you can focus on just that one issue and
understand the specific problem much better.

> You can imagine how many round trips this would take to the server if
> multiple individual UPDATE statements had been run.

If you're not returning data and you reuse the connection like you're supposed
to, "round trips" cost is essentially nothing. What's expensive here is that
the database server has to scan the index or row data on member_status.
However, if the table is not billions of rows, it can probably fit that index
(or even the row data for small tables) in memory and will cache hit on
everything.

However, the list of single UPDATE statement _can_ perform much better than a
monolithic statement. If you're only updating a portion of the table, or if
the number of rows that you'll actually be updating is comparatively small,
then the list of single UPDATE statements can perform _much_ better. It all
depends on exactly what you're doing with the table.

~~~
orf
This comment is really interesting to me as I'm working on getting essentially
this functionality added to Django[1].

The case statement is slightly different, more like this (usually with a
couple of hundred ID's):

    
    
        UPDATE TABLE place 
        SET rating = (
           CASE WHEN id = 1 THEN 25
           CASE WHEN id = 2 THEN 94
           CASE WHEN id = 3 THEN 16
        ) 
        WHERE id IN (1, 2, 3)
    

Members of the Django community, myself included, have seem huge performance
benefits using this technique. I had an update loop go from taking 90 seconds
to 3.

As a seasoned DBA you see any huge downsides with this technique? I agree that
if you're updating the whole table using a CASE statement is not a great idea
but if you want to update 1000 rows with differing values and you have to
issue 1000 individual queries then this can take some time (including whatever
overhead Django has).

1\.
[https://github.com/django/django/pull/9606](https://github.com/django/django/pull/9606)

~~~
pritambaral
> (usually with a couple of hundred ID's)

> I had an update loop go from taking 90 seconds to 3.

How is the performance with prepared statements? It seems like repeatedly
executing the same statement with different values is what prepared statements
are made for.

~~~
Can_Not
The ORM likely does use prepared statements, it's that most ORMs by default
makes 100 round-trip IO operations (one for each update). The code would need
to be rewritten without the ORM to send all the updates as one database call.
The query isn't expensive to the database, but to the application itself just
the IO of the database call adds up quickly.

~~~
orf
Unfortunately Django does not use prepared statements. There are some big
challenges to work around before support can be added. In any case I'd be
very, very surprised if a significant portion of the 87 seconds using a single
statement saved was saved also saved by using a prepared statement.

~~~
Can_Not
I don't think using a prepared statement 100 times will have anywhere close to
the same performance improvement as sending one concatenated bulk update or
one update set case.

~~~
pritambaral
No reason why prepared statements and single-call batched-statements can't
both be used together. Of course, if one method (batching statements) works
well enough, there's no need for another (prepared statements) too.

~~~
Can_Not
In every ORM I've ever used, everything is a prepared statement by default, so
I've only been comparing 100 separate database call updates vs 100 updates
concatenated as a single database call and 100 updates as a single update case
when, where all three were already implied prepared statements.

------
bmpafa
These are great. I feel like no matter how many PG tips & tricks I learn,
there's always something new out there.

I recently started learning PostgreSQL after ~2 yrs of relying on BaaS etc.
(mostly of the NoSQL variety).

I have to say: I regret not learning it earlier. Stuff like constraints,
triggers, views, etc., are game-changers, but even just being able to write
queries instead of navigating a proprietary GUI was worth the few weeks it
took to learn.

It was also a boon for data analysis work. Whereas before I'd take a few hours
cleaning some data with JavaScript or Python, now I just write a SQL query or
two. It's faster, it feels more powerful, and it's _substantially_ less
fragile than one-off data cleaning scripts.

Anyway: for those that started out like I did, afraid of SQL, dive on in.

~~~
cuchoi
How did you learn? Old-fashioned trial and error + googling?

~~~
JohnCohorn
The PG docs are surprisingly good. Good enough to learn from. I remember
learning my first SQL back in like 1999 or 2000 by reading the PG docs in the
back of my parents minivan. Good times!

~~~
paulie_a
I don't mean to knock the pg docs but I honestly come away reading them with
the feeling "what did I just read?". They seem dense and dry. So personally I
appreciate the articles like this to help me really learn. I will skip over
official documentation and go to this sort of content.

~~~
jlg23
> They seem dense and dry.

I consider that a feature. I loathe wasting my time by having to dig through
layers of prose to get some fact.

But they are probably not suitable as a tutorial, they only make sense when
you already know relational database basics and need info on pg-specifics.

~~~
paulie_a
It's a personal preference I guess. I'd rather hit up stack overflow and find
a common English answer to a similar problem that usually includes an example.
Django suffers from the same thing... excellent docs, that just seem
unapproachable day to day.

Basically when I Google something, I'm skipping the first 2-3 official links
that in fact contains the info I need, but instead clicking the one that
summarized it more plainly

~~~
mgkimsal
If it doesn't actually resonate with you, and you don't come away with the
understanding you need, it's most certainly not a 'feature' but a 'bug'. I
think some projects see dense/thicks docs that keep a high barrier to entry as
a feature itself as well.

If they're not approachable, they're not 'excellent'.

~~~
paulie_a
I've been a django developer for a decade and keep hearing from others how
great their docs are. I barely ever use them. My biggest complaint is they
give examples without any context. I can't look up a feature without knowing
12 other things before

------
geocar
PostgreSQL like many SQL engines has real cost in doing separate queries, and
the query analyser does a lot of work to "understand" what you mean (and may
need a lot of guidance in the form of indexes), but in kdb+, a lot of these
queries are simple and obvious to a q programmer, and doing it across multiple
statements creates opportunities to understand what you're looking at -- and
with little-to-no cost.

1.

    
    
        fake:2017.02.01+til 28
        t:select sum amount by legit:payment_date.dd from payment
        t:update fake:legit from a where legit in fake.dd
        select from t where amount > 2000
    

2.

    
    
        string[2]," times "," 2 equals",string[2*2]
    

3.

    
    
        t:desc select sum amount from payment by customer_id where payment_date.mm = 4, payment_date.dd within 10 13
        / or obviously clearer if you know the year:
        / t:select ... where payment_date within 2017.04.10 2017.04.13
        d:exec distinct customer_id from t where amount > 30
        select first_name, last_name, email from customer where customer_id in d
    

only want the top 5?

    
    
        d:5#d;
    

4.

Tables are a data type (unlike SQL) so this is much simpler:

    
    
        tbl_1:([] some_day:2018.04.01+til 15; an_amt:2.43+2.266*til 15)
        tbl_2:([] some_day2:2018.05.16+til 16; an_amt2:15.43+1.03*til 16)
    

5.

No need for a query:

    
    
        count film
        count where film.rating="G"
    

or remembering this is a vector language:

    
    
        sum film.rating="G"
    

count of each rating needs a query though:

    
    
        select count i by rating from film
    

6.

An excellent opportunity to see value sharing syntax between function
application and indexing:

    
    
        m:`gold`bronze`platinum`silver!`gold_group`bronze_group`platinum_group`silver_group
        m:m!`$string[m:`gold`bronze`platinum`silver],\:"_group"; /shorter
    
        update member_status:m member_status from `reward_members where member_status in key m
    

7.

    
    
        awards_to_honor:select expense_amt, member_status from reward_members where member_status=`gold_group
        save `:awards_to_honor.csv

~~~
klibertp
> a lot of these queries are simple and obvious to a Q programmer

I'm not sure if it was you, but I most definitely saw a couple of posts like
this on HN. Each time I had just one wish: for the poster to provide an
explanation, preferably from the basics, of what is going on.

As a programmer, due to my hobby, I learned a lot more programming languages
than average and am pretty confident in my ability to learn new ones, yet K
and Q elude me completely. Every time I get interested in it, I struggle with
its complete lack of entry-level documentation. I learned J, and while it's
similarly impenetrable at first, there are tons of good documentation on J
wiki. With Q I couldn't find anywhere near that amount of explanations.

So, I'm not against pasting Q snippets in comments, but I think they would
bring much, much more value if accompanied with a detailed explanation of both
syntax, semantics, and library functions. As it stands, even things I thought
I know, like that : means assignment, only add to the confusion, because
looking at your code I see that apparently it sometimes means something else
entirely...

~~~
is0tope
I usually recommend Q for Mortals.
V[http://code.kx.com/q4m3/](http://code.kx.com/q4m3/)

The thing I love is that the SQL and regular programming syntax can be mixed.
For instance, if I wish to apply a lambda function for each group I can do,
eg:

select result: {lambda code here...} column by category from table

Since a table is implicitly a list of dictionaries under the hood, you can
iterate over a table where each column will be a dictionary. Tables and
dictionaries are first class objects in q, so it is a lot easier to reason
about certain manipulations as they are essentially the same as if you had
used regular code. One issue I've always had with SQL itself is that I learned
KSQL first, and the way many things are done in regular sql just seem strange.

------
spraak
How does one get this level of SQL proficiency? I am not sure how to go beyond
my current level of understanding, and these queries don't make much sense to
me. I don't often do more than some selects and maybe an inner join
occasionally, so far.

~~~
AlisdairO
(self plug) I made [https://pgexercises.com/](https://pgexercises.com/) a
while back to help people learn SQL in a learn-by-doing exercise format. You
can run the queries in the browser, so it's easy to just to an exercise when
you have a spare few minutes.

~~~
barryhoodlum
I came across this when it was posted to HN a year or two ago and ended up
finishing the whole thing. Thank you for making it.

~~~
AlisdairO
Pleasure - glad you found it useful :-)

------
bmpafa
minor style thought: I think it'd be easier to scroll thru an article like
this (ie, a list of things) if the item headers were more distinct from the
body. e.g., a heavier weight, or maybe with more vertical margin.

~~~
topspin
Indenting the SQL wouldn't hurt either.

~~~
labster
You should view the article on mobile then, where all the SQL is indented...
and in a font 2x the size of the rest of the article, and you can only see 25%
of a line.

------
sudomake
You dont need include aggregate functions in your select clause to use them in
having and order by.

I suppose I may be glossing over another point, but for the subquery example I
would've just done:

SELECT first_name, last_name, email FROM customer WHERE customer_id IN (SELECT
DISTINCT customer_id FROM payment WHERE extract(month from payment_date) = 4
AND extract(day from payment_date) BETWEEN 10 AND 13 GROUP BY customer_id
HAVING SUM(amount) > 30 ORDER BY SUM(amount) DESC LIMIT 5);

------
jaequery
can anyone recommend a really good postgres GUI client? ive not found one yet
for OSX, which is almost baffling

~~~
bmdavi3
Other people have given actual answers, so I'll go ahead and give the non-
answer of... use psql instead!

Like Dimitri Fontaine says early in the Mastering PostgreSQL in Application
Development book mentioned elsewhere in this thread, psql is really powerful.
From the \d meta commands to inspect anything and everything in the database,
to the variable substitution, nothing beats editing sql in your preferred
editor and hitting \i my_query.sql in psql to run it. You can also nest
additional \i my_other_query.sql commands inside the files you run with \i,
which I've found really useful for repeatedly dropping / installing test
tables / views, or setting up test data, before running the query I'm actually
interested in. Take the time to customize it by adding a few lines to your
.psqlrc file, like \x auto, and \timing and it becomes very usable very
quickly.

I used psql for a number of years, then joined a new company where DataGrip
was the norm. I gave DataGrip an honest try to see what I might be missing
from a GUI SQL client. After a year of using it exclusively, I slowly found
myself migrating back to psql and can honestly say I don't miss anything.

But it's also possible I just haven't tried the right GUI client yet. Anyway,
I hope you find the PostgreSQL client that works for you!

~~~
njharman
yes but pgcli, [https://www.pgcli.com/](https://www.pgcli.com/)

~~~
emmelaich
Nice. That tui looks similar to the gcloud alpha interactive cli.

