Category
5 min read

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.
Published on
June 27, 2023
Contributors
Adam Furmanek
Dev Rel
Metis Team
Share this post

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:

TitleBasic.init(
{
	tconst: { type: DataTypes.STRING, primaryKey: true, allowNull: false },
	titletype: { type: DataTypes.TEXT },
	primarytitle: { type: DataTypes.TEXT },
	originaltitle: { type: DataTypes.TEXT },
	isadult: { type: DataTypes.BOOLEAN },
	startyear: { type: DataTypes.INTEGER },
	endyear: { type: DataTypes.INTEGER },
	runtimeminutes: { type: DataTypes.INTEGER },
	genres: { type: DataTypes.TEXT }
},
{
	sequelize,
	modelName: 'TitleBasic',
	tableName: 'title_basics',
	schema: 'imdb',
	timestamps: false,
}
);
TitleRating.init(
{
	tconst: { type: DataTypes.STRING, primaryKey: true, allowNull: false },
	averagerating: { type: DataTypes.DECIMAL},
	numvotes: DataTypes.INTEGER,
},
{
	sequelize,
	modelName: 'TitleRating',
	tableName: 'title_ratings',
	schema: 'imdb',
	timestamps: false,
}
);
TitlePrincipal.init(
{
	tconst: { type: DataTypes.STRING, primaryKey: true, allowNull: false },
	ordering: { type: DataTypes.INTEGER, primaryKey: true, allowNull: false },
	nconst: { type: DataTypes.TEXT },
	category: { type: DataTypes.TEXT },
	job: { type: DataTypes.TEXT },
	characters: { type: DataTypes.TEXT }
},
{
	sequelize,
	modelName: 'TitlePrincipal',
	tableName: 'title_principals',
	schema: 'imdb',
	timestamps: false,
}
);
TitleCrew.init(
{
	tconst: { type: DataTypes.STRING, primaryKey: true, allowNull: false },
	directors: { type: DataTypes.TEXT },
	writers: { type: DataTypes.TEXT }
},
{
	sequelize,
	modelName: 'TitleCrew',
	tableName: 'title_crew',
	schema: 'imdb',
	timestamps: false,
}
);
NameBasic.init(
{
	nconst: { type: DataTypes.STRING, primaryKey: true, allowNull: false },
	primaryname: { type: DataTypes.TEXT },
	birthyear: { type: DataTypes.INTEGER },
	deathyear: { type: DataTypes.INTEGER },
	primaryprofession: { type: DataTypes.TEXT },
	knownfortitles: { type: DataTypes.TEXT }
},
{
	sequelize,
	modelName: 'NameBasic',
	tableName: 'name_basics',
	schema: 'imdb',
	timestamps: false,
}
);

We can see that most of the tables are configured to represent SQL tables directly with no modifications. I’m also using the following associations:

 db.TitleBasic.hasMany(db.TitlePrincipal, {
	foreignKey: 'tconst',
	targetKey: 'tconst',
	as: 'titleBasicTitlePrincipal',
});

db.TitleBasic.hasMany(db.TitlePrincipal, {
	foreignKey: 'tconst',
	targetKey: 'tconst',
	as: 'titleBasicTitlePrincipal2',
});


db.TitlePrincipal.belongsTo(db.TitleBasic, {
	foreignKey: 'tconst',
	targetKey: 'tconst'
});


db.TitlePrincipal.hasOne(db.NameBasic, {
	foreignKey: 'nconst',
	targetKey: 'nconst',
	sourceKey: 'nconst'
});


db.NameBasic.belongsTo(db.TitlePrincipal, {
	foreignKey: 'nconst',
	targetKey: 'nconst',
	sourceKey: 'nconst'
});


db.TitleBasic.hasOne(db.TitleRating, {
	foreignKey: 'tconst',
	targetKey: 'tconst'
});


db.TitleRating.belongsTo(db.TitleBasic, {
	foreignKey: 'tconst',
	targetKey: 'tconst'
});


db.TitleBasic.hasOne(db.TitleCrew, {
	foreignKey: 'tconst',
	targetKey: 'tconst',
});


db.TitleCrew.belongsTo(db.TitleBasic, {
	foreignKey: 'tconst',
	targetKey: 'tconst'
});


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:

return titleBasic
.findAll({
	include: [{
		model: titlePrincipal,
		required: true,
		as: 'titleBasicTitlePrincipal',
		where: {
			'nconst': nconst
		},
	}],
	order: [
		['startyear', 'DESC']
	],
	limit: 10
});

This is the SQL code that has been generated:

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.

Metis optimized queries case study analysis

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:

Metis optimized queries with added index

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:

return titleBasic
	.findAll({
		include: [{
			model: titlePrincipal,
			required: true,
			duplicating: false,
			as: 'titleBasicTitlePrincipal',
			where: {
				'nconst': nconst
			},
		}],
		order: [
			['startyear', 'DESC']
		],
		limit: 10
	});

This is the query we’re getting now:

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:

Metis optimized queries without index 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:

Metis optimized queries with added index insights

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:

return titleBasic
	.findAll({
		include: [
		{
			model: titleRating,
			required: true,
			duplicating: false,
		},
		{
			model: titlePrincipal,
			required: true,
			duplicating: false,
			as: 'titleBasicTitlePrincipal',
			where: {
				'nconst': nconst
			},
		},
		],
		order: [
			[ titleRating, 'averagerating', 'DESC'],
		],
		limit: 10
	});

The code generates the following query:

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:

Metis optimized queries without index insights 2

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:

Metis optimized queries with added index insights 2

Find the ten top rated films with some number of votes

Here is the application code for getting top movies:

return titleBasic
	.findAll({
		include: [
		{
			model: titleRating,
			required: true,
			duplicating: false,
			where: {
				'numvotes': { [Op.gte]: numvotes }
			}
		},
		],
		order: [
			[ titleRating, 'averagerating', 'DESC'],
		]
	});

The code generates the following query:

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:

Metis optimized queries without index insights 3

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:

Metis optimized queries with added index insights 3

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.

Ready to take your database to the next level?

Start using Metis and get your database observability set up in minutes