For the query above, the following recommendations will be helpful as part of the SQL tuning process. You'll find 3 sections below:
Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
Avoid Calling Functions With Indexed Columns (query line: 7): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `name` is indexed, the index won’t be used as it’s wrapped with the function `array_agg`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
Avoid Correlated Subqueries (query line: 6): A correlated subquery is a subquery that contains a reference (column: id) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
Avoid Selecting Unnecessary Columns (query line: 3): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX tags_idx_id ON "tags" ("id");
CREATE INDEX task_tags_idx_task_id ON "task_tags" ("task_id");
The optimized query:
ON (tasks.id) tasks.*
array_agg(tags.name) AS tags
ON task_tags.tag_id = tags.id
task_tags.task_id = tasks.id
tt.tags @> ARRAY['tag1'::varchar, 'tag3'::varchar]