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

相思毛毛豆

我有一个查询，需要9分钟GCC gcc（GCC）4.1.2 20080704（Red Hat
: K% V3 u" e- v+ C" ^4.1.2-46)，64位编译人员x86_64-unknown-linux-gnu上的PostgreSQL 9.0.0上运行
这个查询是由hibernate自动为我的应用程序生成。它试图在一所学校找到所有的教师成员。成员资格是在小组中扮演角色的用户。有几种类型的团队，但在这里重要的是学校和服务。如果有人既是服务教师，也是学校（15499），那么他们就是我们需要的。
查询过去在生产环境中运行良好，但在开发环境中仍然运行良好，但在生产环境中运行需要几分钟。你能帮我理解为什么吗？
9 w8 f* ?$ G0 u/ x这是查询：
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                         and (membership7_.startDate is null or membership7_.startDate 解释分析输出：
; _" ]' E& }  e8 i: X    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 = 1549))                                   Filter: ((NOT deleted) AND (CASE WHEN (expires IS NULL) THEN 1 ELSE CASE WHEN ((expires > now()) AND ((startdate IS NULL) OR (startdate   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   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 =       Filter: (NOT group4_.deleted)Total runtime: 652504.827 ms(29 rows)我正在阅读论坛帖子和用户手册，但除非有可能使用论坛帖子和用户手册，否则我想不出什么能让它运行得更快。now()功能选择创建索引。
0 S. ?1 C! M) x* G  b4 ^: u# R                                                               
    解决方案:                                                               
                                                                我重写了你的查询，以为会更快:
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 = 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.startDate IS NULL OR m.startDate 用重写 EXISTScase ... end = 1用简单的表达式取代了奇怪的表达式
重写所有显式连接语法JOIN，让它更容易阅读。
将大JOIN结构和IN两种表达式转换EXISTS半联接，这使得有必要坦白DISTINCT。这应该快得多。
许多小的修改使查询更容易，但它们并没有改变内容。
: n0 N8 C" ^+ ~尤其要使用simper别名-你的噪音和混乱。指标如果这还不够快，你的写入性能可以处理更多的索引，请添加这部分多列索引：
CREATE INDEX dir_memberships_g_id_u_id_idx ON dir_memberships (group_id,user_id)WHERE  deleted = FALSE;该WHERE符合您查询索引的条件是有用的！
假设你已经在主键和相关外键上有索引了。
进一步：
7 [, c- F: y3 pCREATE INDEX dir_memberships_u_id_role_idx ON dir_memberships (user_id,role)WHERE  deleted = FALSE;为什么user_id第二次？
PostgreSQL索引工作
. ^' Z' S: ]  s: W. k( K7 X. N查询第一个字段的复合索引也有用吗？另外，由于user_id已经在另一个索引中使用，因此您不会阻止HOT更新(只能与不涉及任何索引的列一起使用)。
7 _2 i6 b! L: Y* R% U# E" W为什么role呢？
! J. D( h. Q6 G% L7 h( O我假设这两列都是类型integer(4个字节)。我已经在你的详细问题上看到了，你在操作MAXALIGN8位字节的64位操作系统，所以另一个整数根本不会增加索引。role认为是第二个EXISTS半连接可能有用。
8 H6 U2 S% D$ y; G- D- f: M0 _如果你有很多死亡用户，也可能有帮助:
CREATE INDEX dir_users_id_idx ON dir_users (id)WHERE status = 'active' AND deleted = FALSE;和往常一样，检查EXPLAIN检查索引是否真的被使用。您不希望浪费资源的无用索引。
: T. w8 \' L% |+ a我们斋戒了吗？
当然，所有关于性能优化的常规建议也适用。