
A Tale of Query Optimization - manish_gill
https://parallelthoughts.xyz/2019/05/a-tale-of-query-optimization/
======
malisper
I can definitely relate to the struggle of debugging slow SQL queries. I've
been optimizing Postgres for the last three years for my day job. There's
nothing like spending hours trying to determine why a query is slow, only to
discover some bizarre incantation makes the problem go away.

I had one case where adding OFFSET 0 to a query, which did not change the
semantics of the query, made the query ~10x faster. It turned out that due to
a reference to a large column being inlined in a dozen different places,
Postgres was detoasting[0] that column a dozen times more than it needed to.
Adding the OFFSET 0 prevented the subquery from being inlined and made it so
Postgres only detoasted the column once.

In general, I think optimizing a database is way harder than it needs to be. A
lot of it is black magic and it requires a ton of expertise that many teams
don't have. Even though I've been doing it for years, I still struggle with
figuring out what indexes I should create to make a query fast.

I recently started a startup in an attempt to make optimizing databases
easier. I built a Postgres extension that records information about queries as
they run. It's basically the same information you would get for EXPLAIN
ANALYZE, but you get it for every query at query time. Then, using this
information, I'm able to make specific recommendations about how you should
optimize your database. I can give advice on how you should structure your
schema and what indexes you should create in order to maximize the performance
of your queries. This way, even teams that don't have much in-house Postgres
experience won't have to spend hours or even days trying to figure out how to
make their queries faster.

If this sounds interesting to you, I would be happy to chat. You can reach out
to me at michael@perfalytics.com. It's my goal to use the expertise I've built
up over the last couple of years to make scaling Postgres a truly painless
experience.

[0] [https://malisper.me/postgres-toast/](https://malisper.me/postgres-toast/)

~~~
ChrisSD
Isn't there some irony in fact that SQL was meant to abstract away the "how"
of getting records? If you need to optimize through implementation specific
quirks, then wouldn't a more direct api be preferable?

~~~
malisper
I have a number of thoughts on this. A common claim I've heard is that query
optimizers are better at producing efficient query plans than all but the best
programmers. I can't find any concrete studies on this, but it's plausible.
Databases are great at handling slight variations of a query and a data set.
I've had times where I thought Postgres was going to execute a query one way,
but it wound up executing it in a different way that happened to be more
efficient.

Given that query optimizers are usually better than humans, it makes sense to
use them for the general case. The question then becomes what happens for the
exceptional cases. Cases where the database chooses a bad plan that a human
could tell is obviously going to be slow. For these cases I think it's up to
the database implementors to decide what to do.

Some databases have support for query hints, a way to force your DB to do what
you want. The Postgres team has avoided implementing query hints for two
reasons:

\- In the long term queries that make use of query hints will be suboptimal.
(Personally I don't buy this. I've ran across cases in Postgres where the
planner will only give you a very inefficient query. I would rather use query
hints to get the query to work for the time being than never have a working
query at all.) \- Implementing query hints would overly complicate the
planner. I find this to be pretty reasonable.

As usual, having a query optimizer is a tradeoff. It seems to me like in most
cases it's better to have one than to not.

~~~
petergeoghegan
Even if you assume that the human programmer is better than the query
optimizer, which I think you'll agree is assuming rather a lot, the query
optimizer still has important advantages. It can react to changing conditions.

The optimal query plan for a given query can change as data is added or
deleted. Moreover, there can be multiple optimal query plans at the same time
for "the same query" (or at least the same parameterized SQL statement).

I'm not suggesting that optimizers don't produce bad plans that have to be
worked around. My point is that comparing the optimizer to an expert human
only makes sense as a thought experiment. Most bad query plans are caused by
bad selectivity estimates/stats, not bad cost models.

~~~
ChrisSD
This is starting to sound a lot like the discussions around optimizing C
compilers. Only in that case you can drop into assembly if necessary.

------
gopalv
Having hit this sort of problem too many times, I now recommend a DNS style
FQDN in all my click-stream data.

The domain stored as ".com.google.mail" etc.

This breaks down a lot of these sort of queries with a simple enough
'.com.google%' filter and which gets evaluated internally even on min-max
zones.

Gets turned into ">= '.com.google.' and <= '.com.google.\u0ff'" in byte
comparisons.

Even then it doesn't really help with the array syntax - because the condition
doesn't get pushed there.

~~~
blattimwind
Arguably domain names written the way they usually are has been wrong since
the beginning, for obvious reasons.

------
ccleve
It isn't clear to me why so few of these optimization articles talk about
temporary tables. They are my go-to solution for many situations.

Temporary tables are great because they break the problem down into smaller
pieces. They make the query much more understandable. They are way easier to
debug. They are way less sensitive to failing because the query optimizer
decides to do things differently because table statistics have changed. They
will often eliminate the need for exotic syntax.

You also have the opportunity to add secondary indexes to these tables.

When you create a temporary table, you are essentially doing the work of the
optimizer yourself. That's perfectly ok if you know things about the data that
the optimizer doesn't or hasn't successfully figured out on its own.

They're not for every situation; they only work well when the intermediate
tables are small and fit in memory. But that is very frequently the case.

~~~
whitehouse3
One reason you may not see CTE's or temp tables included in these articles is
because their specifics are tightly coupled to the schema of your DB. It's
hard to write a generic example in Stack Overflow that would work for someone
else.

But like you said: if you know the DB schema then temp tables make SQL easier
to read, write, and reason about.

~~~
oarabbus_
I'm not sure what you mean by this - it is possible to write any subquery as a
CTE

~~~
Someone
Might refer to MySQL. It didn’t support any CTEs until version 8.0, from
April, 2018.

------
jteppinette
Optimizing a large database (billions of rows) has been the bane of my
existence over the last couple months. But, when you finally get that 10x /
100x performance improvement, it’s all worth it... until the next thing.

~~~
kjeetgill
I've recently started working on a service with a MySQL database of a few
billion rows. I'm used to developing on lower KVs like RocksDB or something.

Any broad, general advice? Cool tricks you've learned? Bulk deletes and a
relatively high continuous write rate are killing me right now.

I kinda hate how manual batching deletes and then having to verify them takes.

~~~
ploxiln
Deleting lots of rows is perhaps the biggest weakness you are running into.
What I do for some cases is "LIMIT 4000", sleep for 1 second, and repeat until
no rows affected (tweak the numbers for your situation obviously) (usually
this is for a periodic expiry/cleanup task).

~~~
_vertigo
It’s probably worth mentioning that at least in MySQL, this is dangerous if
using statement based replication.

[https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-
rbr....](https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-
rbr.html#replication-sbr-rbr-sbr-disadvantages)
[https://dev.mysql.com/doc/refman/8.0/en/replication-
features...](https://dev.mysql.com/doc/refman/8.0/en/replication-features-
limit.html)

~~~
ploxiln
Great point.

I happen to use row-format mixed (default on RDS), so that can make it OK.
Also, often I have some other related stuff to cleanup, so the pattern is more
like: SELECT id, stuff FROM table WHERE expired LIMIT 1000; do cleanup; if all
successful: DELETE FROM table WHERE id IN (id, id, ...); so the delete is
deterministic for statement-based replication.

------
slifin
Query optimisation is such an underated skill, imo it should be prioritised as
a core skill for any backend developer

~~~
jjeaff
It seems there are a lot of new "lead" developers out their that don't even
understand rdbms, much less how to optimize them. A lot these days seem to be
coming into the field thinking that SQL is old hat and nosql is the future.

You should see this newly rebuilt e-commerce system I saw recently all built
on mongo. The hoops jumped through and special functions created and work-
arounds were just astounding. They basically had to build their own rdbms
layer in node to interface with their mongo datastore.

All this because the new hotshot developer convinced management that this was
the only way to solve the DB bottlenecks they were having.

...because "MySQL will just never be able to query the product table in less
than a few seconds since it has 'millions' of rows".

An entire system rewrite to solve a problem that probably could have been
solved with a few indexes. And now they are going to have to implement some
things in MySQL again, because all the node.js code is getting overly
complicated.

~~~
codegladiator
> "lead" developers out their that don't even understand rdbms

Just lead ? I am interviewing "Full stack rockstars" who haven't who think SQL
is unfit for every purpose. Frustrating, but a good filter.

------
davidgould
This article could be improved by including the Postgres EXPLAIN output so the
reader could follow along the deductive process.

~~~
web007
Im confused - did they not EXPLAIN their query? That's the first step in ANY
slow query debugging!

I kept waiting to see EXPLAIN output so I could say "oh, there's your table
scan" and nothing ever showed up.

------
ddebernardy
> Then, for a small subset of data, I manually verified that all results were
> correct.

This could have been automated with a full join. with A as queryA, B as queryB
select A full join B on all applicable fields where A is null or B is null.
Alternatively, you could also do: A except B union all B except A. Either way
you get the rows in A that aren't in B and rows in B that aren't in A -- no
manual check needed, and you could have run that on the entire set instead of
or in addition to counting rows.

------
fabian2k
I'm a bit confused why they didn't use EXPLAIN ANALYZE at all in this case.
Debugging performance issues in Postgres without it makes it unnecessarily
hard. I'm not entirely sure without being able to try it myself, but I suspect
it should have immediately identified in sufficient detail where most of the
time in that query was spent .

------
perlgeek
The frustrating thing about such optimizations is how badly they fit with the
rest of the software development and testing workflow.

Ideally, you want to write a test that prevents a performance regression, but
then you need to set up a test database with the right data characteristics
(but not real data, for privacy concerns), need the hardware to handle that,
need to maintain that database through future schema changes and so on.

It would also be nice to have some kind of mechanism that can tell you that
the optimization is no longer necessary when you switch to a new DB version
with an improved optimizer.

Most organizations don't have the engineering capabilities or capacities to
provide this kind of tooling and infrastructure, so the benefits of CI/CD
often don't apply to such changes.

------
michaelmcmillan

        Then, for a small subset of data, I manually verified that all results were correct.
    

Where’s your test suite man? :)

------
AaronFriel
I wonder if a CTE with both sets and `intersect` between them would be more
efficient than &&.

~~~
jteppinette
> [https://www.postgresql.org/docs/11/queries-
> with.html](https://www.postgresql.org/docs/11/queries-with.html) The
> optional RECURSIVE modifier changes WITH from a mere syntactic convenience
> into a feature that accomplishes things not otherwise possible in standard
> SQL.

Am I incorrect in thinking that "standard" CTEs don't have an impact on
performance. When I saw this in the article and how it had no performance
improvements it reaffirmed my assumption.

~~~
wolf550e
PostgreSQL materializes all CTEs so it has a big impact on performance. It's a
hack that works around lack of optimizer hints in PostgreSQL to force the
execution plan you want. Only in PostgreSQL 12 did they change this:
[https://www.postgresql.org/docs/devel/release-12.html#id-1.1...](https://www.postgresql.org/docs/devel/release-12.html#id-1.11.6.5.5.3.4)

~~~
jteppinette
>
> [https://dba.stackexchange.com/a/13117](https://dba.stackexchange.com/a/13117)
> Also, a CTE should never be used for performance. You will almost never
> speed things up by using a CTE, because, again, it's just a disposable view.

~~~
wolf550e
Information about SQL in general should not be confused with information about
specific RDBMS.

That SE answer is about MS SQL Server.

What I wrote was specific to PostgreSQL which does a specific weird thing
using CTEs where they are materialized.

In SQL Server, CTEs are not materialized and are just used to organize the
query, to make it more readable.

If I wrote about PostgreSQL heap organized tables, would you object and say
I'm wrong because MS SQL Server tables are index-organized, clustered on the
primary key?

~~~
hobs
Good answer, but fwiw SQL Server has heaps too :p

------
coleifer
Unbelievable that this is even getting upvotes. Anyone with a basic
understanding of relational databases could see that nested o(n) ilike filter
was the problem. I kept reading in the hopes that maybe something actually
interesting was going on, besides ineptitude. Disappointing.

When you have a huge list like that in a filter, the answer is to treat it
like a table and perform a join. Then the query planner can optimize the
ordering of the loops. But this is just mind-boggling ignorance dressed up as
a "debugging horror story".

~~~
jdreaver
Get over yourself.

This is a well-written article in which the author lays out their thought
process for optimizing a slow SQL query. The article certainly provides more
value to someone who may not be an RDBMS expert than your comment does.

------
thomaswang
I think the root of your issue is the %text% leftsided % is horribly slow.

~~~
zimpenfish
Addressed in the article:

> The pattern matching query itself is taking only 5 seconds independently.
> Matching millions of unique URLs is clearly not a problem.

~~~
Someone
As _thomaswang_ says, that assumes that the query planner recognizes that it
can hoist that query part out of the loop
([https://en.wikipedia.org/wiki/Loop-
invariant_code_motion](https://en.wikipedia.org/wiki/Loop-
invariant_code_motion))

If it doesn’t, and runs it again and again, time will add up (possibly by less
than 5 seconds for each time it gets run because the data could stay in
memory)

That theory was only disproved at “ _Move the sub-query into a CTE_ ” section.

Also, I’m not familiar with Postgres, so I don’t know whether it could
significantly affect timing, but the query benchmarked at 5231.765 ms isn’t
identical to that in the larger query. The latter has _::text[]_ added.

I also would try and replace

    
    
        AND r_time > to_timestamp(1547585600)
        AND r_time < to_timestamp(1549177599)
    

by

    
    
       AND r_time BETWEEN to_timestamp(1547585600+1) AND timestamp(1549177599-1)
    

because the query optimizer might not detect that in this query.

~~~
manish_gill
BETWEEN is soemthing that is on the "Don't do this" page of Postgres. :)

[https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use...](https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_BETWEEN_.28especially_with_timestamps.29)

the ::text[] addition had no impact on perf. I actually constructed this post
from my notes at the time so might missed have a few things here and there.

The bit about query planner recognizing things is exactly what the challenge
is to be honest. Like I said at the end, the good mental model of SQL helps a
lot, and mine is decent-ish I hope but can still learn a lot. :)

~~~
Someone
That’s why I added the _+1_ and _-1_ fragments :-) I thought those computed
the smallest larger/largest smaller time stamp.

I didn’t realize that time stamps weren’t integers in PostgreSQL, though, so
that is a bug.

Are you sure your query, which uses an interval that is open at both ends, is
correct?

------
jjluoma
I find this a little bit funny,

> I was extremely suspicious of the EXISTS optimization, since it changes the
> logic to exit early.

Does this this mean that if one has found an answer, it is somehow sensible
thing not to return the answer right away?

~~~
abraae
With a database, that depends if you want "all the answers" or "any answer".
Exists is the latter.

------
sovnade
Thanks - good investigation. We're replatforming a legacy app from Oracle to
Postgres in the near future and I'm sure we'll see stuff just like this.
Definitely saving so I can refer back.

------
superjisan
This is pretty great. I've been meaning to find a good story and helpful
guidelines for making queries faster as I taught students about SQL.

------
ComodoHacker
A funny bit:

>I started googling for a way to do set-intersection in Postgres without using
the && and it wasn’t going anywhere.

That's what SQL was made for! It's called 'join'. Perhaps is wasn't the best
idea to store session's urls in an array and not in a table. (It's called
normalization.)

