Unlocking IMDB Data With Metis for Awesome Database Optimization Insights

curl --output name.basics.tsv.gz https://datasets.imdbws.com/name.basics.tsv.gz \
	&& curl --output title.akas.tsv.gz https://datasets.imdbws.com/title.akas.tsv.gz \
	&& curl --output title.basics.tsv.gz https://datasets.imdbws.com/title.basics.tsv.gz \
	&& curl --output title.crew.tsv.gz https://datasets.imdbws.com/title.crew.tsv.gz \
	&& curl --output title.episode.tsv.gz https://datasets.imdbws.com/title.episode.tsv.gz \
	&& curl --output title.principals.tsv.gz https://datasets.imdbws.com/title.principals.tsv.gz \
	&& curl --output title.ratings.tsv.gz https://datasets.imdbws.com/title.ratings.tsv.gz

gzip -d name.basics.tsv.gz \
	&& gzip -d title.akas.tsv.gz \
	&& gzip -d title.basics.tsv.gz \
	&& gzip -d title.crew.tsv.gz \
	&& gzip -d title.episode.tsv.gz \
	&& gzip -d title.principals.tsv.gz \
	&& gzip -d title.ratings.tsv.gz

sed 's/\\N//g' name.basics.tsv > name.basics.tsv2 \
	&& sed 's/\\N//g' title.akas.tsv > title.akas.tsv2 \
	&& sed 's/\\N//g' title.basics.tsv > title.basics.tsv2 \
	&& sed 's/\\N//g' title.crew.tsv > title.crew.tsv2 \
	&& sed 's/\\N//g' title.episode.tsv > title.episode.tsv2 \
	&& sed 's/\\N//g' title.principals.tsv > title.principals.tsv2 \
	&& sed 's/\\N//g' title.ratings.tsv > title.ratings.tsv2 \
	&& rm *.tsv

CREATE SCHEMA imdb;

CREATE TABLE imdb.name_basics(
	nconst              TEXT    NOT NULL PRIMARY KEY,
	primaryName        TEXT,
	birthYear          INT,
	deathYear          INT,
	primaryProfession  TEXT,
	knownForTitles    TEXT
);
   
CREATE TABLE imdb.title_akas(
	titleId TEXT NOT NULL,
	ordering INT NOT NULL,
	title TEXT,
	region TEXT,
	language TEXT,
	types TEXT,
	attributes TEXT,
	isOriginalTitle BIT,
	constraint pk_title_akas PRIMARY KEY (titleId, ordering)
);


CREATE TABLE imdb.title_basics(
	tconst TEXT NOT NULL PRIMARY KEY,
	titleType TEXT,
	primaryTitle TEXT,
	originalTitle TEXT,
	isAdult BIT,
	startYear INT,
	endYear INT,
	runtimeMinutes INT,
	genres TEXT
);


CREATE TABLE imdb.title_crew(
	tconst TEXT NOT NULL PRIMARY KEY,
	directors TEXT,
	writers TEXT
);


CREATE TABLE imdb.title_episode(
	tconst TEXT NOT NULL PRIMARY KEY,
	partentTconst TEXT,
	seasonNumber INT,
	episodeNumber INT
);


CREATE TABLE imdb.title_principals (
	tconst TEXT NOT NULL,
	ordering INT NOT NULL,
	nconst TEXT,
	category TEXT,
	job TEXT,
	characters TEXT,
	constraint pk_title_principals PRIMARY KEY (tconst, ordering)
);


CREATE TABLE imdb.title_ratings (
	tconst TEXT NOT NULL PRIMARY KEY,
	averageRating NUMERIC(15, 8),
	numVotes BIGINT
);
CODE

COPY imdb.name_basics FROM '/database/name.basics.tsv2' DELIMITER E'\t' QUOTE E'\b' CSV HEADER;
COPY imdb.title_akas FROM '/database/title.akas.tsv2' DELIMITER E'\t' QUOTE E'\b' CSV HEADER;
COPY imdb.title_basics FROM '/database/title.basics.tsv2' DELIMITER E'\t' QUOTE E'\b' CSV HEADER;
COPY imdb.title_crew FROM '/database/title.crew.tsv2' DELIMITER E'\t' QUOTE E'\b' CSV HEADER;
COPY imdb.title_episode FROM '/database/title.episode.tsv2' DELIMITER E'\t' QUOTE E'\b' CSV HEADER;
COPY imdb.title_principals FROM '/database/title.principals.tsv2' DELIMITER E'\t' QUOTE E'\b' CSV HEADER;
COPY imdb.title_ratings FROM '/database/title.ratings.tsv2' DELIMITER E'\t' QUOTE E'\b' CSV HEADER;

rm *.tsv2
rm *.tsv
rm *.tsv.gz

SELECT *
FROM imdb.name_basics
LIMIT 1

EXPLAIN (VERBOSE, BUFFERS, COSTS, FORMAT JSON)
SELECT *
FROM imdb.name_basics
LIMIT 1

[
{
	"Plan": {
	"Node Type": "Limit",
	"Parallel Aware": false,
	"Async Capable": false,
	"Startup Cost": 0.00,
	"Total Cost": 0.02,
	"Plan Rows": 1,
	"Plan Width": 66,
	"Output": ["nconst", "primaryname", "birthyear", "deathyear", "primaryprofession", "knownfortitles"],
	"Shared Hit Blocks": 0,
	"Shared Read Blocks": 0,
	"Shared Dirtied Blocks": 0,
	"Shared Written Blocks": 0,
	"Local Hit Blocks": 0,
	"Local Read Blocks": 0,
	"Local Dirtied Blocks": 0,
	"Local Written Blocks": 0,
	"Temp Read Blocks": 0,
	"Temp Written Blocks": 0,
	"Plans": [
	{
		"Node Type": "Seq Scan",
		"Parent Relationship": "Outer",
		"Parallel Aware": false,
		"Async Capable": false,
		"Relation Name": "name_basics",
		"Schema": "imdb",
		"Alias": "name_basics",
		"Startup Cost": 0.00,
		"Total Cost": 253832.48,
		"Plan Rows": 12416948,
		"Plan Width": 66,
		"Output": ["nconst", "primaryname", "birthyear", "deathyear", "primaryprofession", "knownfortitles"],
		"Shared Hit Blocks": 0,
		"Shared Read Blocks": 0,
		"Shared Dirtied Blocks": 0,
		"Shared Written Blocks": 0,
		"Local Hit Blocks": 0,
		"Local Read Blocks": 0,
		"Local Dirtied Blocks": 0,
		"Local Written Blocks": 0,
		"Temp Read Blocks": 0,
		"Temp Written Blocks": 0
	}
	]
	},
	"Query Identifier": -599869187636405422,
	"Planning": {
	"Shared Hit Blocks": 0,
	"Shared Read Blocks": 0,
	"Shared Dirtied Blocks": 0,
	"Shared Written Blocks": 0,
	"Local Hit Blocks": 0,
	"Local Read Blocks": 0,
	"Local Dirtied Blocks": 0,
	"Local Written Blocks": 0,
	"Temp Read Blocks": 0,
	"Temp Written Blocks": 0
	}
}
]

EXPLAIN (ANALYZE, TIMING, VERBOSE, BUFFERS, COSTS, FORMAT JSON)
SELECT *
FROM imdb.name_basics
LIMIT 1

SELECT TB.*
FROM name_basics AS NB
LEFT JOIN title_principals AS TP ON TP.nconst = NB.nconst
LEFT JOIN title_basics AS TB ON TB.tconst = TP.tconst
WHERE NB.nconst = 'nm1588970'
ORDER BY TB.startyear DESC
LIMIT 10

CREATE INDEX IF NOT EXISTS title_principals_nconst_idx ON imdb.title_principals(nconst) INCLUDE (tconst);

SELECT TB.*, TR.*
FROM name_basics AS NB
LEFT JOIN title_principals AS TP ON TP.nconst = NB.nconst
LEFT JOIN title_basics AS TB ON TB.tconst = TP.tconst
LEFT JOIN title_ratings AS TR on TR.tconst = TP.tconst
WHERE NB.nconst = 'nm1588970'
ORDER BY TR.averagerating DESC, TR.numvotes DESC
LIMIT 10

SELECT TB.*, TR.*
FROM title_basics AS TB
LEFT JOIN title_ratings AS TR on TR.tconst = TB.tconst
WHERE TR.numvotes > 10000
ORDER BY TR.averagerating DESC
LIMIT 10

CREATE INDEX IDX_title_ratings_637d5836 ON title_ratings (numvotes)

SELECT TB.*
FROM title_basics AS TB
JOIN title_principals AS TP1 ON TP1.tconst = TB.tconst
JOIN title_principals AS TP2 ON TP2.tconst = TB.tconst
WHERE TP1.nconst = 'nm0302368' AND TP2.nconst = 'nm0001908'

CREATE INDEX IF NOT EXISTS title_principals_nconst_idx ON imdb.title_principals(nconst) INCLUDE (tconst);

SELECT DISTINCT NB.*
FROM title_basics AS TB
LEFT JOIN title_principals AS TP ON TP.tconst = TB.tconst
LEFT JOIN title_crew AS TC ON TC.tconst = TB.tconst
LEFT JOIN name_basics AS NB ON
	NB.nconst = TP.nconst
	OR TC.directors = NB.nconst
	OR TC.directors LIKE NB.nconst || ',%'::text
	OR TC.directors LIKE '%,'::text || NB.nconst || ',%'::text
	OR TC.directors LIKE '%,'::text || NB.nconst
	OR TC.writers = NB.nconst
	OR TC.writers LIKE NB.nconst || ',%'::text
	OR TC.writers LIKE '%,'::text || NB.nconst || ',%'::text
	OR TC.writers LIKE '%,'::text || NB.nconst
WHERE TB.tconst = 'tt0000439'

nm0001,nm002,nm003

SELECT MAX(CHAR_LENGTH(writers) - CHAR_LENGTH(REPLACE(writers, ',', '')))
FROM title_crew

WITH RECURSIVE numbers AS (
	SELECT 1 AS number
	UNION ALL
	SELECT number + 1 AS number FROM numbers WHERE number < 1500
),
split_associations AS (
	SELECT SPLIT_PART(TC.directors, ',', N.number) AS nconst
	FROM title_crew AS TC
	CROSS JOIN numbers AS N
	WHERE tconst = 'tt0000439' AND directors IS NOT NULL AND CHAR_LENGTH(directors) - CHAR_LENGTH(REPLACE(directors, ',', '')) + 1 >= N.number
	UNION
	SELECT SPLIT_PART(TC.writers, ',', N.number) AS nconst
	FROM title_crew AS TC
	CROSS JOIN numbers AS N
	WHERE tconst = 'tt0000439' AND writers IS NOT NULL AND CHAR_LENGTH(writers) - CHAR_LENGTH(REPLACE(writers, ',', '')) + 1 >= N.number
),
all_associations AS (
	SELECT SA.nconst
	FROM split_associations AS SA
	UNION
	SELECT TP.nconst
	FROM title_principals AS TP
	WHERE TP.tconst = 'tt0000439'
)
SELECT *
FROM all_associations

WITH RECURSIVE numbers AS (
	SELECT 1 AS number
	UNION ALL
	SELECT number + 1 AS number FROM numbers WHERE number < 1500
),
split_associations AS (
	SELECT SPLIT_PART(TC.directors, ',', N.number) AS nconst
	FROM title_crew AS TC
	CROSS JOIN numbers AS N
	WHERE tconst = 'tt0000439' AND directors IS NOT NULL AND CHAR_LENGTH(directors) - CHAR_LENGTH(REPLACE(directors, ',', '')) + 1 >= N.number
	UNION
	SELECT SPLIT_PART(TC.writers, ',', N.number) AS nconst
	FROM title_crew AS TC
	CROSS JOIN numbers AS N
	WHERE tconst = 'tt0000439' AND writers IS NOT NULL AND CHAR_LENGTH(writers) - CHAR_LENGTH(REPLACE(writers, ',', '')) + 1 >= N.number
),
all_associations AS (
	SELECT SA.nconst
	FROM split_associations AS SA
	UNION
	SELECT TP.nconst
	FROM title_principals AS TP
	WHERE TP.tconst = 'tt0000439'
)
SELECT NB.*
FROM name_basics AS NB
JOIN all_associations AS AA ON AA.nconst = NB.nconst

SELECT NB.nconst, MAX(NB.primaryname), MAX(nb.birthyear), MAX(NB.deathyear), MAX(nb.primaryprofession), COUNT(*) AS number_of_titles
FROM title_basics AS TB
RIGHT JOIN title_principals AS TP ON TP.tconst = TB.tconst
RIGHT JOIN name_basics AS NB ON NB.nconst = TP.nconst
WHERE TB.startyear >= 1900 AND TB.startyear <= 1915
GROUP BY NB.nconst
ORDER BY number_of_titles DESC
LIMIT 1

CREATE INDEX IF NOT EXISTS title_basics_startyear_idx ON imdb.title_basics(startyear) INCLUDE (tconst);
CREATE INDEX IF NOT EXISTS title_principals_tconst_idx ON imdb.title_principals(tconst) INCLUDE (nconst);

WITH best_actor AS (
	SELECT TP.nconst, COUNT(*) AS number_of_titles
	FROM title_basics AS TB
	LEFT JOIN title_principals AS TP ON TP.tconst = TB.tconst
	WHERE TB.startyear >= 1900 AND TB.startyear <= 1915 AND TP.nconst IS NOT NULL
	GROUP BY TP.nconst
	ORDER BY number_of_titles DESC
	LIMIT 1
)
SELECT BA.nconst, BA.number_of_titles, NB.primaryname, nb.birthyear, NB.deathyear, nb.primaryprofession
FROM best_actor AS BA
JOIN name_basics AS NB ON NB.nconst = BA.nconst

SELECT NB.nconst, NB.primaryname, NB.birthyear, COUNT(*) AS movies_count
FROM name_basics AS NB
LEFT JOIN title_principals AS TP ON TP.nconst = NB.nconst
LEFT JOIN title_basics AS TB ON TB.tconst = TP.tconst
WHERE TB.genres = 'Action' OR TB.genres LIKE 'Action,%' OR TB.genres LIKE '%,Action,%' OR TB.genres LIKE '%,Action'
GROUP BY NB.nconst, NB.primaryname, NB.birthyear
ORDER BY movies_count DESC
LIMIT 10

CREATE INDEX title_basics_genres_gin_idx ON title_basics USING gin (genres gin_trgm_ops);

WITH best_actors AS (
	SELECT TP.nconst, COUNT(*) AS movies_count
	FROM title_basics AS TB
	LEFT JOIN title_principals AS TP ON TP.tconst = TB.tconst
	WHERE TB.genres = 'Action' OR TB.genres LIKE 'Action,%' OR TB.genres LIKE '%,Action,%' OR TB.genres LIKE '%,Action'
	GROUP BY TP.nconst
	ORDER BY movies_count DESC
	LIMIT 10
)
SELECT BA.nconst, NB.primaryname, NB.birthyear, BA.movies_count
FROM best_actors AS BA
JOIN name_basics AS NB ON NB.nconst = BA.nconst
ORDER BY movies_count DESC

WITH RECURSIVE numbers AS (
	SELECT 1 AS number
	UNION ALL
	SELECT number + 1 AS number FROM numbers WHERE number < 1500
),
titles_for_person AS (
	SELECT TC.tconst
	FROM title_crew AS TC
	WHERE directors = 'nm0000428' OR directors LIKE 'nm0000428,%' OR directors LIKE '%,nm0000428,%' OR directors LIKE '%,nm0000428'
	UNION
	SELECT TC.tconst
	FROM title_crew AS TC
	WHERE writers = 'nm0000428' OR writers LIKE 'nm0000428,%' OR writers LIKE '%,nm0000428,%' OR writers LIKE '%,nm0000428'
	UNION
	SELECT tconst
	FROM title_principals
	WHERE nconst = 'nm0000428'
),
titles_corresponding AS (
	SELECT TC.tconst, TC.directors, TC.writers
	FROM title_crew AS TC
	JOIN titles_for_person AS TFP ON TFP.tconst = TC.tconst
),
split_associations AS (
	SELECT TC.tconst, SPLIT_PART(TC.directors, ',', N.number) AS nconst
	FROM titles_corresponding AS TC
	CROSS JOIN numbers AS N
	WHERE directors IS NOT NULL AND CHAR_LENGTH(directors) - CHAR_LENGTH(REPLACE(directors, ',', '')) + 1 >= N.number
	UNION
	SELECT TC.tconst, SPLIT_PART(TC.writers, ',', N.number) AS nconst
	FROM titles_corresponding AS TC
	CROSS JOIN numbers AS N
	WHERE writers IS NOT NULL AND CHAR_LENGTH(writers) - CHAR_LENGTH(REPLACE(writers, ',', '')) + 1 >= N.number
),
all_associations AS (
	SELECT SA.tconst, SA.nconst
	FROM split_associations AS SA
	UNION
	SELECT TP.tconst, TP.nconst
	FROM title_principals AS TP
	JOIN titles_for_person AS TFP ON TFP.tconst = TP.tconst
),
other_people AS (
	SELECT nconst
	FROM all_associations
	WHERE nconst != 'nm0000428'
),
top_peers AS (
	SELECT OP.nconst, COUNT(*) as common_titles
	FROM other_people AS OP
	GROUP BY nconst
	ORDER BY common_titles DESC
	LIMIT 5
)
SELECT TP.nconst, TP.common_titles, NB.*
FROM top_peers AS TP
JOIN name_basics AS NB ON NB.nconst = TP.nconst
ORDER BY TP.common_titles DESC

CREATE INDEX title_crew_directors_gist_idx ON title_crew USING gist (directors gist_trgm_ops);
CREATE INDEX title_crew_writers_gist_idx ON title_crew USING gist (writers gist_trgm_ops);

CREATE INDEX IF NOT EXISTS title_principals_nconst_idx ON imdb.title_principals(nconst) INCLUDE (tconst);
CREATE INDEX IF NOT EXISTS title_principals_tconst_idx ON imdb.title_principals(tconst) INCLUDE (nconst);