生产中的PostgreSQL查询速度慢-帮助我理解此解释分析输出

我重写了您的查询，并认为这会更快：

SELECt u.id AS id14_, u.first_name AS first2_14_, u.last_name AS last3_14_, u.street_1 AS street4_14_, u.street_2 AS street5_14_, u.city AS city14_, u.us_state_id AS us7_14_, u.region AS region14_, u.country_id AS country9_14_, u.postal_pre AS postal10_14_, u.user_name AS user11_14_, u.password AS password14_, u.profession AS profession14_, u.phone AS phone14_, u.url AS url14_, u.bio AS bio14_, u.last_login AS last17_14_, u.status AS status14_, u.birthdate AS birthdate14_, u.ageinyears AS ageinyears14_, u.deleted AS deleted14_, u.createdate AS createdate14_, u.audit AS audit14_, u.migrated2008 AS migrated24_14_, u.creator AS creator14_FROM   dir_users u WHERe  u.status = 'active'AND    u.deleted = FALSEAND    EXISTS (   SELECT 1   FROM   dir_memberships m   JOIN   dir_roles       r ON r.id = m.role   JOIN   dir_groups      g ON g.id = m.group_id   WHERe  m.group_id = 15499   AND    m.user_id = u.id   AND   (m.expires IS NULL       OR m.expires > now() AND (m.startdate IS NULL OR m.startdate < now()))   AND    m.deleted = FALSE   AND    r.deleted = FALSE   AND    r.name = 'ROLE_MEMBER'   AND    g.deleted = FALSE   )AND    EXISTS (    SELECT 1    FROM   dir_memberships m    JOIN   dir_roles       r ON r.id = m.role    WHERe (m.expires IS NULL        OR m.expires > now() AND (m.startDate IS NULL OR m.startDate < now()))    AND    m.deleted = FALSE    AND    m.user_id = u.id    AND    r.name = 'ROLE_TEACHER_MEMBER'    )

用重写

EXISTS

• case ... end = 1

  用简单的表达式替换了奇怪的表达式
• 用显式联接语法重写所有JOIN，以使其更易于阅读。
• 将大

  JOIN

  结构和

  IN

  表达式转换为两个

  EXISTS

  半联接，这使的必要性告白

  DISTINCT

  。这应该快得多。
• 许多小的修改使查询更简单，但是它们并没有改变内容。
  尤其要使用simper别名-您的嘈杂和混乱。
  

指标

如果这还不够快，并且您的写入性能可以处理更多索引，请添加此部分多列索引：

CREATE INDEX dir_memberships_g_id_u_id_idx ON dir_memberships (group_id, user_id)WHERe  deleted = FALSE;

该

WHERe

我假设您已经有主键和相关外键上的索引。

进一步：

CREATE INDEX dir_memberships_u_id_role_idx ON dir_memberships (user_id, role)WHERe  deleted = FALSE;

为什么

user_id

• PostgreSQL中的索引工作
• 复合索引对第一个字段的查询是否也有用？

另外，由于

user_id

为什么

role

integer

role

EXISTS

如果您有许多“死亡”用户，这也可能会有所帮助：

CREATE INDEX dir_users_id_idx ON dir_users (id)WHERe status = 'active' AND deleted = FALSE;

与往常一样，检查

EXPLAIN

我们斋戒了吗？

当然，所有有关性能优化的常规建议也适用。