How Metis Optimized Queries Executed by Sequelize - A Case Study
Let’s see how Metis can prevent, monitor, and troubleshoot our databases. In this part we’re going to play with Sequelize, a feature-rich ORM for TypeScript. We are going to see actual insights provided by Metis and how they improve the query performance.
Sequelize is an ORM for Node.js and TypeScript. It can connect to PostgreSQL and other SQL engines. It supports transactions, relations, eager loading, lazy loading, functions, and other common operations.
We are going to use Sequelize to execute the same queries as in Part 1 of this series. It's highly recommended to read that post before moving on.
Data model
First thing we need to do is to model the database. I’m using the following table definitions:
It’s worth noticing that I configured TitleBasics to TitlePrincipals association twice. That’s because one of my queries needs to join TitlePrincipals twice and Sequelize can’t do that just like that.
For a given actor, find their latest movies
Let’s start with our first query. We can implement the application code for getting latest movies for an actor:
SELECT
TitleBasic.*,
titleBasicTitlePrincipal.tconst AS titleBasicTitlePrincipal.tconst,
titleBasicTitlePrincipal.ordering AS titleBasicTitlePrincipal.ordering,
titleBasicTitlePrincipal.nconst AS titleBasicTitlePrincipal.nconst,
titleBasicTitlePrincipal.category AS titleBasicTitlePrincipal.category,
titleBasicTitlePrincipal.job AS titleBasicTitlePrincipal.job,
titleBasicTitlePrincipal.characters AS titleBasicTitlePrincipal.characters
FROM
(
SELECT
TitleBasic.tconst,
TitleBasic.titletype,
TitleBasic.primarytitle,
TitleBasic.originaltitle,
TitleBasic.isadult,
TitleBasic.startyear,
TitleBasic.endyear,
TitleBasic.runtimeminutes,
TitleBasic.genres
FROM
imdb.title_basics AS TitleBasic
WHERE
(
SELECT
tconst
FROM
imdb.title_principals AS titleBasicTitlePrincipal
WHERE
(
titleBasicTitlePrincipal.nconst = 'nm1588970'
AND titleBasicTitlePrincipal.tconst = TitleBasic.tconst
)
LIMIT
1
) IS NOT NULL
ORDER BY
TitleBasic.startyear DESC
LIMIT
10
) AS TitleBasic
INNER JOIN imdb.title_principals AS titleBasicTitlePrincipal ON TitleBasic.tconst = titleBasicTitlePrincipal.tconst AND titleBasicTitlePrincipal.nconst = 'nm1588970'
ORDER BY
TitleBasic.startyear DESC;
That’s a lot. We can see that Sequelize decided to use subqueries to correlate tables. That happens when we join tables and we don’t specify duplicating: false. Sequelize thinks that it will get duplicates and wants to avoid that.
Query executes in nearly 130 seconds and reads 70 million rows. That’s a lot.
We can try improving it by adding the index:
CREATE INDEX IF NOT EXISTS title_principals_nconst_idx ON imdb.title_principals(nconst) INCLUDE (tconst);
Now we get the following:
We can see the index has been used. The query is now faster, but it’s still a terrible performance. Let’s remove the index and see if we can do better. Let’s add the duplicating flag:
SELECT
TitleBasic.tconst,
TitleBasic.titletype,
TitleBasic.primarytitle,
TitleBasic.originaltitle,
TitleBasic.isadult,
TitleBasic.startyear,
TitleBasic.endyear,
TitleBasic.runtimeminutes,
TitleBasic.genres,
titleBasicTitlePrincipal.tconst AS titleBasicTitlePrincipal.tconst,
titleBasicTitlePrincipal.ordering AS titleBasicTitlePrincipal.ordering,
titleBasicTitlePrincipal.nconst AS titleBasicTitlePrincipal.nconst,
titleBasicTitlePrincipal.category AS titleBasicTitlePrincipal.category,
titleBasicTitlePrincipal.job AS titleBasicTitlePrincipal.job,
titleBasicTitlePrincipal.characters AS titleBasicTitlePrincipal.characters
FROM
imdb.title_basics AS TitleBasic
INNER JOIN imdb.title_principals AS titleBasicTitlePrincipal ON TitleBasic.tconst = titleBasicTitlePrincipal.tconst AND titleBasicTitlePrincipal.nconst = 'nm1588970'
ORDER BY
TitleBasic.startyear DESC
LIMIT
10;
It looks much more decent. Just a regular join. Let’s see the insights:
It’s still reading a lot of data, but runs much faster (in 60 seconds). If we add the index, then we get the following performance:
This is great. We can also send the raw query with the following:
return sequelize.query(`CREATE INDEX IF NOT EXISTS title_principals_nconst_idx ON imdb.title_principals(nconst) INCLUDE (tconst)`).then(() =>
sequelize.query(`
SELECT TitleBasic.*
FROM imdb.title_basics AS TitleBasic
JOIN imdb.title_principals AS TitlePrincipals ON TitlePrincipals.tconst = TitleBasic.tconst
WHERE TitlePrincipals.nconst = :nconst
ORDER BY TitleBasic.startyear DESC
LIMIT 10
`, {
model: titleBasic,
mapToModel: true,
replacements: {
nconst: nconst
},
})
);
For a given actor, find their ten most highly rated films
Let’s now find the best movies for an actor. This is the code we can use:
SELECT
TitleBasic.tconst,
TitleBasic.titletype,
TitleBasic.primarytitle,
TitleBasic.originaltitle,
TitleBasic.isadult,
TitleBasic.startyear,
TitleBasic.endyear,
TitleBasic.runtimeminutes,
TitleBasic.genres,
TitleRating.tconst AS TitleRating.tconst,
TitleRating.averagerating AS TitleRating.averagerating,
TitleRating.numvotes AS TitleRating.numvotes,
titleBasicTitlePrincipal.tconst AS titleBasicTitlePrincipal.tconst,
titleBasicTitlePrincipal.ordering AS titleBasicTitlePrincipal.ordering,
titleBasicTitlePrincipal.nconst AS titleBasicTitlePrincipal.nconst,
titleBasicTitlePrincipal.category AS titleBasicTitlePrincipal.category,
titleBasicTitlePrincipal.job AS titleBasicTitlePrincipal.job,
titleBasicTitlePrincipal.characters AS titleBasicTitlePrincipal.characters
FROM
imdb.title_basics AS TitleBasic
INNER JOIN imdb.title_ratings AS TitleRating ON TitleBasic.tconst = TitleRating.tconst
INNER JOIN imdb.title_principals AS titleBasicTitlePrincipal ON TitleBasic.tconst = titleBasicTitlePrincipal.tconst AND titleBasicTitlePrincipal.nconst = 'nm1588970'
ORDER BY
TitleRating.averagerating DESC
LIMIT
10;
The query runs in 35 seconds, and these are the insights:
Let’s now add the index:
CREATE INDEX IF NOT EXISTS title_principals_nconst_idx ON imdb.title_principals(nconst) INCLUDE (tconst);
And now the query runs immediately:
Find the ten top rated films with some number of votes
Here is the application code for getting top movies:
SELECT
TitleBasic.tconst,
TitleBasic.titletype,
TitleBasic.primarytitle,
TitleBasic.originaltitle,
TitleBasic.isadult,
TitleBasic.startyear,
TitleBasic.endyear,
TitleBasic.runtimeminutes,
TitleBasic.genres,
TitleRating.tconst AS TitleRating.tconst,
TitleRating.averagerating AS TitleRating.averagerating,
TitleRating.numvotes AS TitleRating.numvotes
FROM
imdb.title_basics AS TitleBasic
INNER JOIN imdb.title_ratings AS TitleRating ON TitleBasic.tconst = TitleRating.tconst AND TitleRating.numvotes >= '10000'
ORDER BY
TitleRating.averagerating DESC;
We get the following insights:
Similarly to our first part, we can see table scans instead of indexes. Let’s add the index that Metis suggests:
CREATE INDEX IDX_imdb_title_ratings_7a4c4d1e1b2 ON imdb.title_ratings (numvotes);
This is what we get now:
We can see that we return way too many rows. Apart from that, all is good.
Summary
That’s it for now. In the next part, we are going to see more queries with Sequelize. They will be much more complex and sophisticated, however, Metis will give us enough help to optimize them. Stay tuned!
This is some text inside of a div block. This is some text inside of a div block. This is some text inside of a div block. This is some text inside of a div block. This is some text inside of a div block.
