How Postgresql analysis helped to gain 290 times performance boost for a particular request

SELECT "episodes".* FROM "episodes" 
INNER JOIN "seasons"
ON "seasons"."id" = "episodes"."season_id"
INNER JOIN "series"
ON "series"."id" = "seasons"."series_id"
WHERE (series.name ILIKE '%friends%'
OR episodes.name ILIKE '%friends%')
Table "public.series"  Column   |            Type             
-----------+-----------------------------
id | integer
name | citext
created_at | timestamp without time zone
updated_at | timestamp without time zone
Indexes:
"series_pkey" PRIMARY KEY, btree (id)
"index_series_on_name_gin_trgm" gin (name gin_trgm_ops)
Table "public.seasons" Column | Type
-----------+-----------------------------
id | integer
created_at | timestamp without time zone
updated_at | timestamp without time zone
number | integer
series_id | integer
Indexes:
"seasons_pkey" PRIMARY KEY, btree (id)
"index_seasons_on_series_id" btree (series_id)
Table "public.episodes" Column | Type
-----------+-----------------------------
id | integer
name | citext
created_at | timestamp without time zone
updated_at | timestamp without time zone
season_id | integer
Indexes:
"series_pkey" PRIMARY KEY, btree (id)
"index_episodes_on_name_gin_trgm" gin (name gin_trgm_ops)
"index_episodes_on_seasons_id" btree (season_id)
=# explain analyze 
SELECT "episodes".* FROM "episodes"
INNER JOIN "seasons"
ON "seasons"."id" = "episodes"."season_id"
INNER JOIN "series"
ON "series"."id" = "seasons"."series_id"
WHERE (series.name ILIKE '%friends%'
OR episodes.name ILIKE '%friends%');
QUERY PLAN
---------------------------------------------------------------
Hash Join (cost=1681.89..178323.06 rows=9372 width=604) (actual time=21.474..2033.074 rows=422 loops=1)
Hash Cond: (episodes.season_id = seasons.id)
Join Filter: ((series.name ~~* '%friends%'::citext) OR (episodes.name ~~* '%friends%'::citext))
Rows Removed by Join Filter: 920117-> Seq Scan on episodes (cost=0.00..154827.81 rows=2058881 width=604) (actual time=0.039..555.465 rows=2058882 loops=1)
-> Hash (cost=1339.35..1339.35 rows=27403 width=22) (actual time=20.981..20.981 rows=27432 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1692kB-> Hash Join (cost=484.28..1339.35 rows=27403 width=22) (actual time=5.404..16.126 rows=27432 loops=1)
Hash Cond: (series.id = season.series_id)
-> Seq Scan on seasons (cost=0.00..477.94 rows=27494 width=8) (actual time=0.006..3.005 rows=27523 loops=1)
-> Hash (cost=289.68..289.68 rows=15568 width=22) (actual time=5.319..5.319 rows=15568 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 956kB-> Seq Scan on series (cost=0.00..289.68 rows=15568 width=22) (actual time=0.007..2.606 rows=15568 loops=1)Planning time: 4.426 ms
Execution time: 2033.295 ms
(15 rows)
Table "public.series"  Column   |            Type             
-----------+-----------------------------
id | integer
name | citext
created_at | timestamp without time zone
updated_at | timestamp without time zone
Indexes:
"series_pkey" PRIMARY KEY, btree (id)
"index_series_on_name" btree (name)
"index_series_on_name_gin_tsvector" gin (to_tsvector('english'::regconfig, name::text))
SELECT "episodes".* FROM "episodes" 
INNER JOIN "seasons"
ON "seasons"."id" = "episodes"."season_id"
INNER JOIN "series"
ON "series"."id" = "seasons"."series_id"
WHERE
(to_tsvector('english', series.name) @@ plainto_tsquery('friedns')
OR to_tsvector('english', episodes.name) @@ plainto_tsquery('friends')
=# explain analyze
SELECT "episodes".* FROM "episodes"
INNER JOIN "seasons"
ON "seasons"."id" = "episodes"."season_id"
INNER JOIN "series"
ON "series"."id" = "seasons"."series_id"
WHERE
(to_tsvector('english', series.name) @@ plainto_tsquery('friends')
OR to_tsvector('english', episodes.name) @@ plainto_tsquery('friends');
QUERY PLAN--------------------------------------------------------------------Hash Join (cost=1681.89..1117828.09 rows=9372 width=613) (actual time=27.600..9885.350 rows=409 loops=1)Hash Cond: (episodes.season_id = seasons.id)Join Filter: ((to_tsvector('english'::regconfig, (series.name)::text) @@ plainto_tsquery('friends'::text)) OR (to_tsvector('english'::regconfig, (episodes.name)::text) @@ plainto_tsquery('friends'::text)))Rows Removed by Join Filter: 920130-> Seq Scan on episodes (cost=0.00..154827.82 rows=2058882 width=613) (actual time=0.041..601.468 rows=2058882 loops=1)
-> Hash (cost=1339.35..1339.35 rows=27403 width=36) (actual time=23.603..23.603 rows=27432 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1692kB-> Hash Join (cost=484.28..1339.35 rows=27403 width=36) (actual time=5.835..18.186 rows=27432 loops=1)
Hash Cond: (series.id = seasons.series_id)
-> Seq Scan on seasons (cost=0.00..477.94 rows=27494 width=8) (actual time=0.035..2.805 rows=27523 loops=1)
-> Hash (cost=289.68..289.68 rows=15568 width=36) (actual time=5.784..5.784 rows=15568 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 956kB-> Seq Scan on series (cost=0.00..289.68 rows=15568 width=36) (actual time=0.005..2.504 rows=15568 loops=1)Planning time: 2.662 ms
Execution time: 9885.518 ms
(15 rows)
=# explain analyze
SELECT "episodes".* FROM "episodes"
INNER JOIN "seasons"
ON "seasons"."id" = "episodes"."season_id"
INNER JOIN "series"
ON "series"."id" = "seasons"."series_id"
WHERE
(to_tsvector('english', series.name) @@ plainto_tsquery('banana')
AND to_tsvector('english', episodes.name) @@ plainto_tsquery('banana');
QUERY PLAN--------------------------------------------------------------------
Nested Loop (cost=13.57..13073.82 rows=24 width=613) (actual time=1.248..8.646 rows=82 loops=1)
-> Nested Loop (cost=13.14..753.63 rows=137 width=4) (actual time=0.894..2.926 rows=14 loops=1)
-> Bitmap Heap Scan on series (cost=12.85..173.36 rows=78 width=4) (actual time=0.048..0.073 rows=13 loops=1)
Recheck Cond: (to_tsvector('english'::regconfig, (name)::text) @@ plainto_tsquery('banana'::text))Heap Blocks: exact=13-> Bitmap Index Scan on index_series_on_name_gin_tsvector (cost=0.00..12.83 rows=78 width=0) (actual time=0.042..0.042 rows=13 loops=1)
Index Cond: (to_tsvector('english'::regconfig, (name)::text) @@ plainto_tsquery('banana'::text))
-> Index Scan using index_seasons_on_series_id on seasons (cost=0.29..7.42 rows=2 width=8) (actual time=0.217..0.217 rows=1 loops=13)
Index Cond: (series_id = series.id)
-> Index Scan using index_episodes_on_season_id on episodes (cost=0.43..89.92 rows=1 width=613) (actual time=0.268..0.399 rows=6 loops=14)
Index Cond: (season_id = seasons.id)
Filter: (to_tsvector('english'::regconfig, (name)::text) @@ plainto_tsquery('banana'::text))Rows Removed by Filter: 8
Planning time: 0.907 ms
Execution time: 8.739 ms
(15 rows)
SELECT "episodes".* FROM "episodes" 
INNER JOIN "seasons"
ON "seasons"."id" = "episodes"."season_id"
INNER JOIN "series"
ON "series"."id" = "seasons"."series_id"
WHERE (to_tsvector('english', episodes.name) @@ plainto_tsquery('friends'))
UNION SELECT "episodes".* FROM "seasons"
INNER JOIN "series" ON "series"."id" = "seasons"."series_id"
INNER JOIN "episodes" ON "seasons"."id" = "episodes"."season_id"
WHERE (to_tsvector('english', series.name) @@ plainto_tsquery('friends'))
Table "public.episodes"    Column   |            Type             
-------------+-----------------------------
id | integer
name | citext
created_at | timestamp without time zone
updated_at | timestamp without time zone
season_id | integer
common_name | character varying
Indexes:
"series_pkey" PRIMARY KEY, btree (id)
"index_episodes_on_name" btree (name)
"index_episodes_on_common_name_gin_trgm" gin (common_name gin_trgm_ops)
"index_episodes_on_season_id" btree (season_id)
explain analyze 
SELECT "episodes"."name"
FROM "episodes" WHERE
episodes.common_name ILIKE '%friends%';
QUERY PLAN--------------------------------------------------------------------Bitmap Heap Scan on episodes (cost=109.58..904.27 rows=204 width=32) (actual time=3.169..7.504 rows=905 loops=1)Recheck Cond: ((combined_name)::text ~~* '%friends%'::text)
Heap Blocks: exact=792
-> Bitmap Index Scan on index_episodes_on_common_name_gin_trgm (cost=0.00..109.53 rows=204 width=0) (actual time=3.071..3.071 rows=905 loops=1)
Index Cond: ((combined_name)::text ~~* '%friends%'::text)Planning time: 7.433 ms
Execution time: 7.667 ms
(7 rows)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store