How Metis Optimized Queries Executed by GORM - A Case Study
Let’s see how Metis can prevent, monitor, and troubleshoot our databases. In this part, we’re going to play with GORM, a feature-rich ORM for GORM. We are going to see actual insights provided by Metis and how they improve the query performance.
GORM is an ORM for GO. 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 GORM 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:
We can see that most of the tables are configured to represent SQL tables directly with no modifications.
For a given actor, find their latest movies
Let’s start with our first query. We can implement the application code for getting the latest movies for an actor:
var result []entities.TitleBasic
db.
Model(&entities.TitleBasic{}).
Joins("LEFT JOIN imdb.title_principals ON title_principals.tconst = title_basics.tconst").
Where("nconst = ?", nconst).
Order("startyear DESC").
Limit(10).
Find(&result)
return result
This is the SQL code that has been generated:
SELECT
title_basics.tconst,
title_basics.titletype,
title_basics.primarytitle,
title_basics.originaltitle,
title_basics.isadult,
title_basics.startyear,
title_basics.endyear,
title_basics.runtimeminutes,
title_basics.genres
FROM
imdb.title_basics
LEFT JOIN imdb.title_principals ON title_principals.tconst = title_basics.tconst
WHERE
nconst = $1
ORDER BY
startyear DESC
LIMIT
10 /*traceparent='00-995e30076455e5b520e3c96e7a3beb51-ca0bafe038e9c2e9-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 actual execution plan. We read nearly 60 million rows and return only two of them. Let’s see the plan visualization.
Statistics were pretty accurate. The engine expected to return 2 rows from title_basics and this is exactly what we have.
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:
All insights are green, as shown below.
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:
var result []entities.TitleBasic
db.
Model(&entities.TitleBasic{}).
Joins("LEFT JOIN imdb.title_ratings ON title_ratings.tconst = title_basics.tconst").
Joins("LEFT JOIN imdb.title_principals ON title_principals.tconst = title_basics.tconst").
Where("nconst = ?", nconst).
Order("averagerating DESC").
Limit(10).
Find(&result)
return result
The code generates the following query:
SELECT
title_basics.tconst,
title_basics.titletype,
title_basics.primarytitle,
title_basics.originaltitle,
title_basics.isadult,
title_basics.startyear,
title_basics.endyear,
title_basics.runtimeminutes,
title_basics.genres
FROM
imdb.title_basics
LEFT JOIN imdb.title_ratings ON title_ratings.tconst = title_basics.tconst
LEFT JOIN imdb.title_principals ON title_principals.tconst = title_basics.tconst
WHERE
nconst = $1
ORDER BY
averagerating DESC
LIMIT
10 /*traceparent='00-29a8eca3f7f15613822c9011157c346d-e6d3e5e62196dfb3-01'*/
The query runs in 7 seconds, and these are the insights:
We can see that we again read nearly 60 million rows. That’s the visualization:
Let’s now add the index and see if it helps:
CREATE INDEX IF NOT EXISTS title_principals_nconst_idx ON imdb.title_principals(nconst) INCLUDE (tconst);
And now the query runs immediately:
We can also check whether the index has been used:
As we can see, the estimated plan included the index.
Find the ten top-rated films with some number of votes
Let’s get ten top-rated movies with a minimal number of votes. Here is the application code for getting top movies:
var result []entities.TitleBasic
db.
Model(&entities.TitleBasic{}).
Joins("LEFT JOIN imdb.title_ratings ON title_ratings.tconst = title_basics.tconst").
Where("numvotes > ?", numVotes).
Order("averagerating DESC").
Find(&result)
return result
The code generates the following query:
SELECT
title_basics.tconst,
title_basics.titletype,
title_basics.primarytitle,
title_basics.originaltitle,
title_basics.isadult,
title_basics.startyear,
title_basics.endyear,
title_basics.runtimeminutes,
title_basics.genres
FROM
imdb.title_basics
LEFT JOIN imdb.title_ratings ON title_ratings.tconst = title_basics.tconst
WHERE
numvotes > $1
ORDER BY
averagerating DESC /*traceparent='00-c9e63d086e57f10a23a87c4ddd443435-b081025e6918f4da-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 IF NOT EXISTS IDX_title_ratings 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 GORM. 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.
