I run specs in discourse and find some inefficient queries in the query log as follows:
1.Unnecessary DISTINCT makes query slowsite_settings_controller.rb#L165as follows:
SELECT
DISTINCT users.id
FROM
"users" CROSS
JOIN categories c
LEFT JOIN category_users cu ON users.id = cu.user_id
AND c.id = cu.category_id
WHERE
(
c.id = '3613'
AND cu.notification_level IS NULL
)
When categories.id and notification_level are specify value, because of UNIQUE (category_id, user_id) in categories_users and PRIMARY KEY(id) in categories, both cross join and left join will not create duplicate
records which means that we can remove DISTINCT to accelerate query as shown below:
This opt query take 4532166 nanosecond(improve 30%)
2.Unnecessary DISTINCT in subquery makes query slowsearch.rb#L523
SELECT
"posts".*
FROM
"posts"
JOIN (
SELECT
*,
row_number() over() row_number
FROM
(
SELECT
topics.id,
min(posts.post_number) post_number
FROM
"posts"
INNER JOIN "post_search_data" ON "post_search_data"."post_id" = "posts"."id"
INNER JOIN "topics" ON "topics"."id" = "posts"."topic_id"
AND ("topics"."deleted_at" IS NULL)
LEFT JOIN categories ON categories.id = topics.category_id
WHERE
("posts"."deleted_at" IS NULL)
AND "posts"."post_type" IN (1, 2, 3)
AND (topics.visible)
AND (
topics.archetype <> 'private_message'
)
AND (
topics.id IN (
SELECT
DISTINCT(tt.topic_id)
FROM
topic_tags tt
WHERE
tt.tag_id in (
SELECT
tag_id
FROM
tag_group_memberships
WHERE
tag_group_id = 504
)
)
)
AND (
categories.id NOT IN (
SELECT
categories.id
WHERE
categories.search_priority = 1
)
)
AND (
(categories.id IS NULL)
OR (NOT categories.read_restricted)
)
GROUP BY
topics.id
ORDER BY
MAX(posts.created_at) DESC
LIMIT
6 OFFSET 0
) xxx
) x ON x.id = posts.topic_id
AND x.post_number = posts.post_number
WHERE
("posts"."deleted_at" IS NULL)
ORDER BY
row_number
DISTINCT(tt.topic_id) is redundant and we can remove it to accelerate queryas shown below:
It can improve performance of this query from 12655768 to 5005154(improve 60%)
17.Unnecessary DISTINCT in subquery makes query slow[search.rb#L642]
SELECT
"posts".*
FROM
"posts"
JOIN (
SELECT
*,
row_number() over() row_number
FROM
(
SELECT
topics.id,
posts.post_number
FROM
"posts"
INNER JOIN "post_search_data" ON "post_search_data"."post_id" = "posts"."id"
INNER JOIN "topics" ON "topics"."id" = "posts"."topic_id"
AND ("topics"."deleted_at" IS NULL)
LEFT JOIN categories ON categories.id = topics.category_id
WHERE
("posts"."deleted_at" IS NULL)
AND "posts"."post_type" IN (1, 2, 3)
AND (topics.visible)
AND (
topics.archetype <> 'private_message'
)
AND (
topics.category_id IN (3715)
)
AND (
topics.id IN (
SELECT
DISTINCT(tt.topic_id)
FROM
topic_tags tt,
tags
WHERE
tt.tag_id = tags.id
AND lower(tags.name) IN ('lunch')
)
)
AND (
(categories.id IS NULL)
OR (NOT categories.read_restricted)
)
ORDER BY
posts.like_count DESC
LIMIT
6 OFFSET 0
) xxx
) x ON x.id = posts.topic_id
AND x.post_number = posts.post_number
WHERE
("posts"."deleted_at" IS NULL)
ORDER BY
row_number
Similar to last (different source code) DISTINCT(tt.topic_id) is redundant and we can remove it to accelerate query:
It can improve performance of this query from 23659762 to 21030593(improve 10%).