Improve issues search perfomance on GraphQL
What does this MR do?
Use project search optimization when searching issues with graphql.
This prevents the timeout described on #34226 (closed)
Test results
It has been verified that using CTE the query does not timeout anymore in production.
Before
EXPLAIN ANALYZE SELECT "issues".*
FROM "issues"
WHERE "issues"."project_id" = 278964
AND (
"issues"."title" ilike '%issue%'
AND "issues"."title" ilike '%bug%'
AND "issues"."title" ilike '%opening%'
AND "issues"."title" ilike '%count%'
OR "issues"."description" ilike '%issue%'
AND "issues"."description" ilike '%bug%'
AND "issues"."description" ilike '%opening%'
AND "issues"."description" ilike '%count%')
ORDER BY "issues"."updated_at" DESC,
"issues"."id" DESC limit 5 \g
ERROR: canceling statement due to statement timeout
After
EXPLAIN ANALYZE WITH "issues" AS (
(
SELECT "issues".*
FROM "issues"
WHERE "issues"."project_id" = 278964))
SELECT "issues".*
FROM issues
WHERE (
"issues"."title" ilike '%issue%'
AND "issues"."title" ilike '%bug%'
AND "issues"."title" ilike '%opening%'
AND "issues"."title" ilike '%count%'
OR "issues"."description" ilike '%issue%'
AND "issues"."description" ilike '%bug%'
AND "issues"."description" ilike '%opening%'
AND "issues"."description" ilike '%count%')
ORDER BY "issues"."updated_at" DESC,
"issues"."id" DESC limit 5
QUERY PLAN
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
----------------------
Limit (cost=54682.21..54682.21 rows=1 width=1264) (actual time=2074.013..2074.024 rows=5 loops=1)
CTE issues
-> Index Scan using index_issues_on_project_id_and_iid on issues issues_1 (cost=0.44..53074.96 row
s=40181 width=780) (actual time=0.021..126.052 rows=37160 loops=1)
Index Cond: (project_id = 278964)
-> Sort (cost=1607.25..1607.26 rows=1 width=1264) (actual time=2074.011..2074.019 rows=5 loops=1)
Sort Key: issues.updated_at DESC, issues.id DESC
Sort Method: top-N heapsort Memory: 35kB
-> CTE Scan on issues (cost=0.00..1607.24 rows=1 width=1264) (actual time=127.175..2073.587 ro
ws=174 loops=1)
Filter: ((((title)::text ~~* '%issue%'::text) AND ((title)::text ~~* '%bug%'::text) AND ((
title)::text ~~* '%opening%'::text) AND ((title)::text ~~* '%count%'::text)) OR ((description ~~* '%issue
s%'::text) AND (description ~~* '%bug%'::text) AND (description ~~* '%opening%'::text) AND (description ~
~* '%count%'::text)))
Rows Removed by Filter: 36986
Planning time: 0.336 ms
Execution time: 2085.916 ms
In the future we could also consider adding a minimal char length even when attempt_project_search_optimizations
is set to true. When using only small words the execution time can go up more than one second, but i am not sure how badly this could affect UX.
EXPLAIN ANALYZE WITH "issues" AS (
(
SELECT "issues".*
FROM "issues"
WHERE "issues"."project_id" = 278964))
SELECT "issues".*
FROM issues
WHERE (
"issues"."title" ilike '%of%'
AND "issues"."title" ilike '%and%'
AND "issues"."title" ilike '%at%'
AND "issues"."title" ilike '%a%'
OR "issues"."description" ilike '%of%'
AND "issues"."description" ilike '%and%'
AND "issues"."description" ilike '%at%'
AND "issues"."description" ilike '%a%')
ORDER BY "issues"."updated_at" DESC,
"issues"."id" DESC limit 5
QUERY PLAN
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
Limit (cost=54686.95..54686.96 rows=5 width=1264) (actual time=3536.066..3536.072 rows=5 loops=1)
CTE issues
-> Index Scan using index_issues_on_project_id_and_iid on issues issues_1 (cost=0.44..53077.50 row
s=40183 width=780) (actual time=0.027..162.243 rows=37160 loops=1)
Index Cond: (project_id = 278964)
-> Sort (cost=1609.45..1609.77 rows=128 width=1264) (actual time=3536.064..3536.067 rows=5 loops=1)
Sort Key: issues.updated_at DESC, issues.id DESC
Sort Method: top-N heapsort Memory: 33kB
-> CTE Scan on issues (cost=0.00..1607.32 rows=128 width=1264) (actual time=0.149..3529.273 ro
ws=20987 loops=1)
Filter: ((((title)::text ~~* '%of%'::text) AND ((title)::text ~~* '%and%'::text) AND ((tit
le)::text ~~* '%at%'::text) AND ((title)::text ~~* '%a%'::text)) OR ((description ~~* '%of%'::text) AND (
description ~~* '%and%'::text) AND (description ~~* '%at%'::text) AND (description ~~* '%a%'::text)))
Rows Removed by Filter: 16173
Planning time: 0.301 ms
Execution time: 3541.368 ms
(12 rows)
Edited by Felipe Cardozo