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

Yulia Oletskaya
May 7, 2018 · 7 min read
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)

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade