How Metis Optimized Queries Executed by SQLAlchemy - A Case Study
Let’s see how Metis can prevent, monitor, and troubleshoot our databases. In this part, we’re going to play with SQLAlchemy, a feature-rich ORM for Python. We are going to see actual insights provided by Metis and how they improve the query performance.
SQLAlchemy is an ORM for Python. 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 SQLAlchemy to execute the same queries as in Part 1 of this series.
Data model
The first thing we need to do is to model the database. I’m using the following table definitions:
SELECT
imdb.title_basics.tconst,
imdb.title_basics.titletype,
imdb.title_basics.primarytitle,
imdb.title_basics.isadult,
imdb.title_basics.startyear,
imdb.title_basics.endyear,
imdb.title_basics.runtimeminutes,
imdb.title_basics.genres
FROM
imdb.title_basics
JOIN imdb.title_principals ON imdb.title_basics.tconst = imdb.title_principals.tconst
WHERE
imdb.title_principals.nconst = 'nm1588970'
ORDER BY
imdb.title_basics.startyear DESC
LIMIT
10 /*controller='titlesForAnActor',db_driver='psycopg2',db_framework='sqlalchemy%3A0.35b0',framework='flask%3A2.2.2',route='/titlesForAnActor',traceparent='00-3ec0aae37dbde0930b61f755039323ca-598bb0d6e50466cb-01'*/
We can see the query just joined two tables and filtered the rows. The query executes in nearly 70 seconds and reads millions of rows. That’s a lot. This is what Metis shows for the analysis:
This is the estimated execution plan. Let’s see exactly what was planned:
Notice that the database engine expected to read only a couple hundred rows. The reality is much worse than that.
We can try improving the query 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:
This is great. We can see that we managed to reduce the execution time to milliseconds thanks to Metis.
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
imdb.title_basics.tconst,
imdb.title_basics.titletype,
imdb.title_basics.primarytitle,
imdb.title_basics.isadult,
imdb.title_basics.startyear,
imdb.title_basics.endyear,
imdb.title_basics.runtimeminutes,
imdb.title_basics.genres
FROM
imdb.title_basics
JOIN imdb.title_ratings ON imdb.title_ratings.tconst = imdb.title_basics.tconst
WHERE
imdb.title_ratings.numvotes >= '10000'
ORDER BY
imdb.title_ratings.averagerating DESC /*controller='highestRatedMovies',db_driver='psycopg2',db_framework='sqlalchemy%3A0.35b0',framework='flask%3A2.2.2',route='/highestRatedMovies',traceparent='00-d84bd6dc38f6d4429a8368e054fb1037-9d57a33a0fcb207e-01'*/
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 the index was used. Let’s see the insights:
We see that we return a very big result size. Apart from that, all is good.
Summary
That’s it for now. In the next part, we are going to see more queries with SQLAlchemy. 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.
