
Ways to Tweak Slow SQL Queries - helenanders26
http://www.helenanderson.co.nz/sql-query-tweaks/
======
rosser
Heed the caution about CTEs being optimization fences. I had a case just this
past week, where one of our engineers needed help with a query that used a
LEFT JOIN against a CTE which generated text-search vectors for some subset of
rows, in some large table. The query was taking 9-10 minutes, cache-warmed.

Used that way — an outer join against a CTE — the planner was forced to
generate a plan that produced a row for every candidate row in the table being
used as input for the text-search, whether or not it would be needed, visiting
nearly all of its pages.

Just by moving the CTE inline (that is: "LEFT JOIN ( #{ subquery } ) AS
blah"), the query completed in 17ms.

This way, the planner could apply conditions from the rest of the query to the
subplan from which text-search vector rows were being produced, such that it
only pages containing rows it already knew it would care about would even be
retrieved.

The rest of this article is pretty on-point, too.

Source: This stuff is my day job.

EDIT: As a counter-point, because they are incredibly useful, I've also had
countless cases where rewriting a query to _use_ CTEs was the several orders
of magnitude win. This also wasn't the only possible fix; the qualifying
conditions in the CTE could have been improved, eliminating the extra work
where it would have occurred.

Like all things computers, the real answer is, "it depends..."

~~~
war1025
I think I read somewhere that they are fixing some of optimization issues for
CTEs in the next release of Postgres. Does anyone know whether that's true or
not?

~~~
rosser
Are you thinking of the "[NOT] MATERIALIZED" clause?

Yes, that's coming.

[https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=608b167f9f9c4553c35bb1ec0eab9ddae643989b)

~~~
war1025
Thanks for the info! I am a big fan of the readability of CTEs. Though I guess
in production code we use SqlAlchemy, so it would just be a matter of swapping
`.cte()` for `.subquery()` probably...

------
julian37
The best strategy isn't mentioned: learning how to read EXPLAIN output and
acting on it.

> Using wildcards at the start and end of a LIKE will slow queries down

That's only true if you don't have the right indexes in place. A wildcard at
the end can use a regular (btree) index. Even a LIKE query with wildcards in
other places can be fast with a trigram index.

[https://niallburkley.com/blog/index-columns-for-like-in-
post...](https://niallburkley.com/blog/index-columns-for-like-in-postgres/)

~~~
janpot
> A wildcard at the end can use a regular (btree) index.

depending on the locale your db is in, it might be a little less "regular".
recently fell in that trap.

[http://blog.cleverelephant.ca/2016/08/pgsql-text-pattern-
ops...](http://blog.cleverelephant.ca/2016/08/pgsql-text-pattern-ops.html)

------
craig_asp
"Pull out the names of only the columns you need instead of using SELECT * to
speed things up further."

This is not performance-related in most cases. Unless the bottleneck is in the
amount of data being transferred to a client over a network connection and
there is a large amount of columns, or you have an index which matches the
limited column list query exactly, there would be no performance difference in
SELECT * vs SELECT <column list>.

In columnar dbs it does matter because the less columns you select the less
data gets accessed on disk. However, this does not hold true for row stores
because data is stored in such a way that the whole row gets accessed no
matter how many columns get specified in the query.

There are many other good reasons why SELECT * is acceptable only in
development queries, but performance is not one of them.

~~~
danielbarla
In terms of performance, the primary rationale for only retrieving columns you
need would be to allow the database engine to select a proper index to use,
specifically, one that covers all the columns you are making use of. This can
actually have a massive impact on the execution plan, far more than simply
fetching say 2x more data, as you point out.

In fact, I'd go so far as to say that the practice of selecting all columns
makes it practically impossible to make use of covering indexes for high
impact queries.

~~~
n0tme
It's not about choosing the right index. The point here is that the database
can only read the index (without going into the table) if all the columns you
need and use in where clause are in the index. Usually index segments are
smaller, so it could be faster. I am talking from the Oracle perspective, but
I believe PostgreSQL would work in a similar way.

~~~
danielbarla
Yes, of course, indexes work like that with where clauses. However here I was
referring to covering indexes, where the columns in your _select_ are also
present (after the ones in the where clause / join / predicate). This prevents
having to do extra seeks into the clustered index to fetch that data, and can
make very significant differences, especially if row counts are high.

Clearly, this type of index is both expensive to maintain, and the benefits
are lessened if you include every column in a table in the covering index. So,
to actually have real benefits, you need to have carefully crafted queries
which only select what they need, and these being matched to carefully crafted
covering indexes. Basically, you cannot have these - which are one of the
better weapons you have for performance - if you always select everything.

~~~
n0tme
I am talking about INDEX FAST FULL SCAN (Oracle) for example. If you do
'select distinct last_name from users;' and you have an index on users
(last_name) you do not need to read the table at all. It is enough to read the
index.

Otherwise it does not matter how many columns you select, because you will
have to read all the columns for every row anyway.

~~~
adamzochowski
> Otherwise it does not matter how many columns you select, because you will
> have to read all the columns for every row anyway.

It does matter. An index can actually contain a cache of values for some
columns. This is done with INCLUDE statement.

    
    
         CREATE INDEX idx
             ON book ( author_id )
             INCLUDE ( book_title )
    
    

This select will use only index:

    
    
           select author_id, book_title
           from books
           where author_id = 123
    
    

This select will use index, and then have to follow and fetch data out of
rows:

    
    
           select *
           from books
           where author_id = 123
    

[https://use-the-index-luke.com/blog/2019-04/include-columns-...](https://use-
the-index-luke.com/blog/2019-04/include-columns-in-btree-indexes)

------
feike
Good set of tips, however "Not NOT IN" is not true in general, as a single
counterexample:

    
    
        EXPLAIN (costs off)
        SELECT relname, relpages
          FROM pg_class
         WHERE relname IN ('pg_class');
    
                             QUERY PLAN
      ---------------------------------------------------------
       Index Scan using pg_class_relname_nsp_index on pg_class
         Index Cond: (relname = 'pg_class'::name)
      (2 rows)
    
    

In general, I'd like to echo other's comments, learning how to use EXPLAIN and
how to interpret query plans is the single most important tool, as it allows
you to verify your hypothesis instead of relying on rules-of-thumb.

~~~
takeda
I actually had situation where the using IN resulted in a query that run for
35s and after replacing with `ANY(VALUES(...` it took ~600ms.

In my case `column IN ('value1', 'value2')` was converted to an array like
this: `((column)::text ANY ('{value1,value2}'::text[]))`, an index on the
column was completely ignored and PG performed a sequential scan.

After changing it to `column = ANY (VALUES ('value1'),('value2'))` it created
a table in memory and used it when performing an index scan.

I have feeling this is maybe some kind of a bug in query planner? This is on
PG 9.6.8.

~~~
jtc331
I think I'd need more information to diagnose; were there any NULL values in
the list? How many values?

~~~
takeda
The column can have nulls but after a quick check there's only a single record
with a null value (probably added as a test, no idea).

In the bad plan a sequential scan was performed and then it removed over 4
million rows (Rows Removed by Filter: 4562849)

Edit: here is the query planner's output:
[https://explain.depesz.com/s/eVXI](https://explain.depesz.com/s/eVXI)

After rewriting the query (change IN into ANY(VALUES( and adding an index to
the "seven" table):
[https://explain.depesz.com/s/b4kU](https://explain.depesz.com/s/b4kU)

~~~
jtc331
I think adding the index to the “seven” table is probably the more meaningful
change.

You said the index was ignored, but based on that plan output the planner made
the right choice since the filter on the “sierra_zulu” table (only one there
with an ANY so I assume that’s the one) matches over 90% of the rows. An index
scan on that would be far worse.

Adding an index on a different table allowed the driving side of the join to
be different, and, with already prefiltered data allowed the other index to be
useful as well (fewer index lookups). I’d bet with that index addition your IN
clause works just fine.

~~~
takeda
If you look at sierra_zulu (line #3 on both plans) it took almost 22 seconds
initially and after query rewrite (and no index change) took 713ms.

So that change of query alone reduced query time from 35s to ~5s, after that
the seven table become the bottleneck and adding an index there reduced the
whole query down to 750ms.

I should have saved the intermediate plan to illustrate it, but it essentially
looked same as the second plan, except there was a sequence scan on seven
table.

------
frgotmylogin
Some of this is probably SQL server specific, but we were having some scaling
issues on legacy code earlier this year and I got a lot of bang for my buck
checking for these things before diving in to specific issues:

\- code with a high row count table variable when a temp table would give more
accurate execution plans due to better cardinality estimates.

\- ultra-complex join criteria with lots of OR logic that performed better as
a UNION

\- lazy function calling, where a developer used a function that did more than
they needed and could be replaced with a simple join to a table

\- looped calls to insert procs that could be done in bulk

There was another entire category of problem that I guess would be described
as "I hate sets" and was mostly attributable to one former employee. These
were recognizable immediately upon opening the code and were a nightmare.

~~~
neighbour
>There was another entire category of problem that I guess would be described
as "I hate sets" and was mostly attributable to one former employee. These
were recognizable immediately upon opening the code and were a nightmare.

Care to elaboarate? I need a pick-me-up today.

~~~
frgotmylogin
It was a lot of stuff like

\- Get a list of x in a table var

\- while loop through x to build another list of y

\- while loop through y and update z one row at a time

All of that rather than updating with a join.

Some of his other patterns were reusing variables for different things in a
proc, using inefficient functions in a way that they executed once per row
before the result set was really reduced much, nesting those functions, and
using loops any chance he got.

You'd open up these slow, 500-700 line monster procs and have to figure out
what they were doing and refactor them, but it was nearly illegible and there
were no tests for them. Really a great reminder of what happens when code
reviews aren't done.

~~~
neighbour
That's way worse than I thought, thanks for sharing.

------
takeda
About indexes, they improve query performance, but they also decrease write
performance (and of course take space) that's why it's good idea to remove
indices that aren't used or rarely used. Especially indices that are placed on
columns that have random data and/or are frequently updated (prevents HOT
updates) can hurt write performance.

~~~
danmaz74
One very powerful "trick" is to use partial indexes where useful. For example,
a pretty common pattern is to have tables where most rows aren't actively used
most of the time, eg when you have a _status_ column, and most rows are in the
final status which you usually don't touch, so most where conditions include
statuses which are not the final one. If you create a partial index WHERE
status IN ('s1', 's2' ...), the index becomes a lot smaller and a lot faster.

~~~
james_s_tayler
I never thought about that, but that's a neat trick.

------
reacweb
The first principle for SQL optimization should be to reduce the number of
queries. If you have a query inside a loop, the performances may be
catastrophic. Using conditionals in queries was sometimes the only solution to
avoid loops. The performance gain was enormous.

------
JacKTrocinskI
Lightly put, the article leaves much to be desired, no mentioned of: execution
plan, collecting statistics, partitioning, cost of indexing (e.g. when should
you disable an index and rebuild it?), materialized views, etc.

~~~
jambalaya
Surprised they didn't mention citext columns

------
racecar789
> If you are calling multiple views, or worse, views on views on views you are
> asking the query engine to run multiple queries to return your columns.

I thought databases flatten all view layers into a single SQL statement before
executing. Aka no performance impact except for a couple milliseconds to
flatten out the views.

~~~
radiowave
Yes, this was my thought as well. The views being flattened is certainly what
I've found it's always looked like was happening, based on the EXPLAIN output.

------
lordnacho
Number one has got to be look at the execution plan. If it's complicated, try
to simplify it, and that's where all these particular things should be
checked.

~~~
JacKTrocinskI
Agreed, related point, collecting statistics.

------
caf
_Try to avoid the use of ‘IN’ or ‘NOT IN’. By doing this you are performing a
full table scan as the query engine looks through every row to check if the
condition is met._

It's not clear to me why IN () can't do an index scan or hash join?

~~~
danmaz74
IN () definitely does use indexes in most cases on Postgres. The article is
wrong there.

~~~
aiCeivi9
Yes in general but I was hit with case when Postgres started to switch to FTS
depending on argument count inside `IN ()` while in reality using index was
faster for each and every case. I had to split it in multiple queries with 100
args each, even if few month earlier it didn't have any issue with thousands.

~~~
jtc331
I’m guessing the index in question was a partial index defined with a “WHERE
column NOT NULL” restriction?

This was a limit of the optimizer previously (IN clauses are broken down into
AND/OR groups to prove inferences but only if <= 100 items).

But Postgres 12 includes a patch I wrote so that the optimizer can prove the
NOT NULL inference directly from an array operator of any size.

~~~
takeda
In my case the index is not a partial index, but it is also on PG 9.6.8, in
another response[1] I provided more details.

Unfortunately we are stuck with 9.6.x since before my time my company decided
to use AWS Aurora, currently there's no easy path to do major version upgrade
from 9.6.x and anyway 10.x is the most recent available version :( but any
information why is this happening would be appreciated.

[1]
[https://news.ycombinator.com/item?id=20863418](https://news.ycombinator.com/item?id=20863418)

------
jasoneckert
This article has sage advice, but SQL optimization goes far beyond.
Optimization is a knee-jerk reaction for me whenever I do database development
and involves every level (e.g. should I use IsNull or Coalesce? Try both, look
at execution plans, etc.)

------
neves
These articles are fine, but it is very rare to see people using a profiler to
discover what needs to be optimized.

------
darkwater
It should be somewhere reflected that this article applies in part only to
PostgreSQL (some of the tips are more generic thou)

~~~
grzm
Immediately following the list TOC:

> _”This is written with PostgreSQL in mind but if you’re using something
> else, these may still help, give them a try._

