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

Question

我有一个查询，它需要9分钟才能在由GCC gcc（GCC）4.1.2 20080704（Red Hat
4.1.2-46），64位编译的x86_64-unknown-linux-gnu上的PostgreSQL 9.0.0上运行

这个查询是由hibernate为我的应用程序自动生成的。它试图找到一所学校中的所有“教师成员”。成员资格是在组中具有角色的用户。团体有几种类型，但是在这里重要的是学校和服务。如果某人既是某服务的教师成员又是这所学校的成员（15499），那么他们就是我们所需要的。

该查询过去在生产环境中运行良好，而在开发环境中仍然运行良好，但是在生产环境中，现在要花费几分钟才能运行。你能帮我理解为什么吗？

这是查询：

select distinct user1_.ID as ID14_, user1_.FIRST_NAME as FIRST2_14_, user1_.LAST_NAME as LAST3_14_, user1_.STREET_1 as STREET4_14_, user1_.STREET_2 as STREET5_14_, user1_.CITY as CITY14_, user1_.us_state_id as us7_14_, user1_.REGION as REGION14_, user1_.country_id as country9_14_, user1_.postal_code as postal10_14_, user1_.USER_NAME as USER11_14_, user1_.PASSWORD as PASSWORD14_, user1_.PROFESSION as PROFESSION14_, user1_.PHONE as PHONE14_, user1_.URL as URL14_, user1_.bio as bio14_, user1_.LAST_LOGIN as LAST17_14_, user1_.STATUS as STATUS14_, user1_.birthdate as birthdate14_, user1_.ageInYears as ageInYears14_, user1_.deleted as deleted14_, user1_.CREATEDATE as CREATEDATE14_, user1_.audit as audit14_, user1_.migrated2008 as migrated24_14_, user1_.creator as creator14_ 
from DIR_MEMBERSHIPS membership0_ 
inner join DIR_USERS user1_ on membership0_.USER_ID=user1_.ID, DIR_ROLES role2_, DIR_GROUPS group4_ 
where membership0_.role=role2_.ID 
and membership0_.GROUP_ID=group4_.id 
and membership0_.GROUP_ID=15499 
and case when membership0_.expires is null 
    then 1 
    else case when (membership0_.expires > CURRENT_TIMESTAMP and (membership0_.startDate is null or membership0_.startDate < CURRENT_TIMESTAMP)) 
        then 1 
        else 0 end 
    end =1 
and membership0_.deleted=false 
and role2_.deleted=false 
and role2_.NAME='ROLE_MEMBER' 
and group4_.deleted=false 
and user1_.STATUS='active' 
and user1_.deleted=false 
and (membership0_.USER_ID in (
    select membership7_.USER_ID 
    from DIR_MEMBERSHIPS membership7_, DIR_USERS user8_, DIR_ROLES role9_ 
    where membership7_.USER_ID=user8_.ID 
    and membership7_.role=role9_.ID 
    and case when membership7_.expires is null 
        then 1 
        else case when (membership7_.expires > CURRENT_TIMESTAMP 
                        and (membership7_.startDate is null or membership7_.startDate < CURRENT_TIMESTAMP)) 
            then 1 
            else 0 end 
        end =1 
    and membership7_.deleted=false 
    and role9_.NAME='ROLE_TEACHER_MEMBER'));

解释分析输出：

 HashAggregate  (cost=61755.63..61755.64 rows=1 width=3334) (actual time=652504.302..652504.307 rows=4 loops=1)
   ->  Nested Loop  (cost=4355.35..61755.56 rows=1 width=3334) (actual time=304.450..652504.217 rows=6 loops=1)
     ->  Nested Loop  (cost=4355.35..61747.28 rows=1 width=3342) (actual time=304.419..652504.060 rows=6 loops=1)
           ->  Nested Loop Semi Join  (cost=4355.35..61738.97 rows=1 width=32) (actual time=304.385..652503.961 rows=6 loops=1)
                 Join Filter: (user_id = user_id)
                 ->  Nested Loop  (cost=0.00..32.75 rows=1 width=16) (actual time=0.190..26.703 rows=758 loops=1)
                       ->  Seq Scan on dir_roles role2_  (cost=0.00..1.25 rows=1 width=8) (actual time=0.032..0.038 rows=1 loops=1)
                             Filter: ((NOT deleted) AND ((name)::text = 'ROLE_MEMBER'::text))
                       ->  Index Scan using dir_memberships_role_group_id_index on dir_memberships membership0_  (cost=0.00..31.49 rows=1 width=24) (actual time=0.151..25.626 rows=758 loops=1)
                             Index Cond: ((role = role2_.id) AND (group_id = 15499))
                             Filter: ((NOT deleted) AND (CASE WHEN (expires IS NULL) THEN 1 ELSE CASE WHEN ((expires > now()) AND ((startdate IS NULL) OR (startdate < now()))) THEN 1 ELSE 0 END END = 1))
                 ->  Nested Loop  (cost=4355.35..61692.86 rows=1069 width=16) (actual time=91.088..843.967 rows=79986 loops=758)
                       ->  Nested Loop  (cost=4355.35..54185.33 rows=1069 width=8) (actual time=91.065..555.830 rows=79986 loops=758)
                             ->  Seq Scan on dir_roles role9_  (cost=0.00..1.25 rows=1 width=8) (actual time=0.006..0.013 rows=1 loops=758)
                                   Filter: ((name)::text = 'ROLE_TEACHER_MEMBER'::text)
                             ->  Bitmap Heap Scan on dir_memberships membership7_  (cost=4355.35..53983.63 rows=16036 width=16) (actual time=91.047..534.236 rows=79986 loops=758)
                                   Recheck Cond: (role = role9_.id)
                                   Filter: ((NOT deleted) AND (CASE WHEN (expires IS NULL) THEN 1 ELSE CASE WHEN ((expires > now()) AND ((startdate IS NULL) OR (startdate < now()))) THEN 1 ELSE 0 END END = 1))
                                   ->  Bitmap Index Scan on dir_memberships_role_index  (cost=0.00..4355.09 rows=214190 width=0) (actual time=87.050..87.050 rows=375858 loops=758)
                                         Index Cond: (role = role9_.id)
                       ->  Index Scan using dir_users_pkey on dir_users user8_  (cost=0.00..7.01 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=60629638)
                             Index Cond: (id = user_id)
           ->  Index Scan using dir_users_pkey on dir_users user1_  (cost=0.00..8.29 rows=1 width=3334) (actual time=0.011..0.011 rows=1 loops=6)
                 Index Cond: (id = user_id)
                 Filter: ((NOT deleted) AND ((status)::text = 'active'::text))
     ->  Index Scan using dir_groups_pkey on dir_groups group4_  (cost=0.00..8.28 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=6)
           Index Cond: (group4_.id = 15499)
           Filter: (NOT group4_.deleted)
Total runtime: 652504.827 ms
(29 rows)

我正在阅读和阅读论坛帖子和用户手册，但是我想不出什么使它运行得更快，除非有可能为使用该now()功能的选择创建索引。

Answer 1

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

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_code 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 = FALSE
AND    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已经在另一个索引中使用，因此您不会阻止HOT更新（只能与不涉及任何索引的列一起使用）。

为什么role呢？
我假设这两列都是类型integer（4个字节）。我已经在您的详细问题中看到，您运行的是MAXALIGN8位字节的64位操作系统，因此另一个整数根本不会使索引增长。我role认为对于第二个EXISTS半联接可能有用。

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

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

与往常一样，检查EXPLAIN以查看索引是否真正被使用。您不希望浪费资源的无用索引。

我们斋戒了吗？

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