originally posted on medium
Tl;dr: Avoid joins on large tables and evaluate parts of queries beforehand to get 100–10,000x performance gains!
As mentioned in a previous post, because of some of our tables growing in size, our queries started performing poorly which resulted in a performance hit to our most used APIs. It was time we revisit some of these queries and do something that will give us the best possible outcome with the least effort.
Our old query (that took 29 seconds to run) was something on the lines of:
EXPLAIN ANALYSE and explain.depesz.com to get an idea of the query that was being run. The reason our queries were running so slowly was:
- In our case, there was a Hash Join taking place, which would create a hash table from rows of one of the candidate tables which match the
join predicate. Now this table can be quickly used for a lookup with the rows of the other candidate in the JOIN. But if we do this for two very large tables (50m and 150m rows), it would mean a lot of memory being used up for the intermediate hash, as well as a lot of rows from the other candidate being looked up against this hash table.
- Appropriate indices weren’t being used in the prepared queries. That could be due to various reasons.
Armed with the knowledge, we thought that if we could just remove the
JOIN from the query, it should return faster.
We basically had to convert:
column_value IN (1, 2, 3) is the result of the
JOIN_PREDICATE ran separately before.
Our experiments showed us that there were huge performance gains. Our queries went down from taking 29 seconds to a few milliseconds!
I don’t believe you
Let’s create two tables:
user can have multiple
The code for creating the tables and inserting data is as follows:
What is the query for?
We want to get all the purchases for the given account IDs.
Run 1: Join Query
Here is the
EXPLAIN ANALYSE output for this query: https://explain.depesz.com/s/kGP
Time taken: 100 seconds
Run 2: Evaluate and Select
Here is the
EXPLAIN ANALYSE output for this query: https://explain.depesz.com/s/9dE
Total Time taken: 7 ms
Join Query: 100 seconds
Evaluate and Select: 7milliseconds
Performance Gain: 10,000x
- Tested on
- Huge gains only when the
join predicatematches 100+ rows, otherwise performance will be more or less the same in both the cases.