基于PostgreSQL的SQL查询优化
基于PostgreSQL的SQL查询优化
提问于 2022-08-12 13:03:21
我正在尝试优化这个sql查询，所用的总时间约为12秒。因此，我使用了解释分析，在上面它显示嵌套循环，它的成本和实际时间，我知道它是一个算法，它用于迭代行，即145854。我试过索引，但它似乎没有太大的影响，即使解释分析显示它确实使用它，如果有人可以建议在查询中的任何变化。请查看所附查询及其结果。
explain analyse
SELECT
"feedback_option"."feedback_id" AS Col2,
"feedback_option"."feedback_created_at" AS Col3,
"feedback_option"."feedback_stage_id" AS Col4,
"feedback_option"."option_id" AS Col5,
"feedback_option"."other_text" AS Col6,
"feedback_option"."duration" AS Col7,
"feedback_option"."back_counter" AS Col8,
"feedback_option"."is_spam" AS Col9,
"feedback_option"."dx" AS Col10,
"feedback_option"."dy" AS Col11,
"feedback_option"."dz" AS Col12,
"feedback_option"."created_at" AS Col13,
"feedback_option"."ts" AS Col14,
"feedback_option"."contact_name" AS Col15,
"feedback_option"."contact_number" AS Col16,
NULLIF(TRIM("feedback_option"."other_text"), '') AS "annotated_other_text",
(feedback.created_at at time zone 'Asia/Karachi') AS "created_at_tz",
EXTRACT('year' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_year",
EXTRACT('isoyear' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_iso_year",
EXTRACT('quarter' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_quarter",
EXTRACT('month' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_month",
EXTRACT('week' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_week",
EXTRACT('dow' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') + 1 AS "created_at_tz_week_day",
EXTRACT('day' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_day",
EXTRACT('hour' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_hour",
EXTRACT('minute' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_minute",
EXTRACT('second' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') AS "created_at_tz_second",
EXTRACT('dow' FROM (feedback.created_at at time zone 'Asia/Karachi') AT TIME ZONE 'UTC') + 1 AS "week_day",
((feedback.created_at at time zone 'Asia/Karachi'))::time AS "created_time"
FROM "feedback_option"
INNER JOIN "question_option"
ON ("feedback_option"."option_id" = "question_option"."id")
INNER JOIN "question"
ON ("question_option"."question_id" = "question"."id")
INNER JOIN "feedback"
ON ("feedback_option"."feedback_id" = "feedback"."id")
INNER JOIN "questionnaire"
ON ("feedback"."questionnaire_id" = "questionnaire"."id")
INNER JOIN "questionnaires_questionnairerole"
ON ("questionnaire"."id" = "questionnaires_questionnairerole"."questionnaire_id")
WHERE (NULLIF(TRIM("feedback_option"."other_text"), '') IS NOT NULL
AND "question"."kind" = 'COM'
AND "feedback"."processed" = true
AND "feedback"."division_id" IN (
SELECT U0."id"
FROM "division" U0
WHERE U0."id" IN (
WITH RECURSIVE division_descendents AS (
SELECT id, parent_id
FROM division
WHERE id IN (2)
UNION SELECT child.id, child.parent_id
FROM division AS child
INNER JOIN division_descendents AS parent ON parent.id = child.parent_id
WHERE child.is_active = True
)
SELECT id
FROM division_descendents
)
)
AND "feedback"."organization_id" = 2
AND "questionnaires_questionnairerole"."role_id" = 173
AND "feedback"."organization_id" = 2
AND "feedback"."questionnaire_id" = 183
AND "feedback"."group_id" = 1
AND "feedback"."processed" = true
)下面是它显示的查询计划
回答 1
Stack Overflow用户
发布于 2022-09-17 17:34:45
当我们在where命令之后使用函数时，例如lower(name)='tom'，DB就不能使用索引。建议在这些情况下使用表达式索引。例子：CREATE INDEX test_a2_idx ON test USING btree (lower(name))你有一个这样的。WHERE (NULLIF(TRIM("feedback_option"."other_text"), '') IS NOT NULL
在IN命令中，如果您的子查询结果是许多记录，那么建议使用INNER JOIN而不是IN。例如:使用
select * from t1 inner join t2 on t1.id = t2.id 而不是
select * from t1 where t1.id in (select id from t2)如果您总是在查询中使用child.is_active = True和其他条件，则可以使用部分索引来优化和减少索引大小。例如：
CREATE INDEX division_parentid_idx ON division USING btree (parent_id) where is_active = true 如果可能，尝试应用上述选项，然后查看结果。我想一定有什么不同吧。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/73334622
复制相关文章
相似问题
腾讯云开发者
