title: Slow PostgreSQL query in production - help me understand this explain analyze output
body: <p>I have a query that is taking 9 minutes to run on PostgreSQL 9.0.0 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit</p>
  
  <p>This query is automatically generated by <a href="/questions/tagged/hibernate" class="post-tag" title="show questions tagged &#39;hibernate&#39;" rel="tag">hibernate</a> for my application. It's trying to find all of the "teacher members" in a school. A membership is a user with a role in a group. There are several types of groups, but here what matters are schools and services. If someone is a teacher member in a service and a member in this school (15499) then they are what we are looking for.</p>
  
  <p>This query used to run fine in production and still runs fine in development, but in production it is now taking several minutes to run. Can you help me understand why?</p>
  
  <p>Here's the query:</p>
  
  <pre><code>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 &gt; CURRENT_TIMESTAMP and (membership0_.startDate is null or membership0_.startDate &lt; 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 &gt; CURRENT_TIMESTAMP 
                          and (membership7_.startDate is null or membership7_.startDate &lt; CURRENT_TIMESTAMP)) 
              then 1 
              else 0 end 
          end =1 
      and membership7_.deleted=false 
      and role9_.NAME='ROLE_TEACHER_MEMBER'));
  </code></pre>
  
  <p>Explain analyze output:</p>
  
  <pre><code> HashAggregate  (cost=61755.63..61755.64 rows=1 width=3334) (actual time=652504.302..652504.307 rows=4 loops=1)
     -&gt;  Nested Loop  (cost=4355.35..61755.56 rows=1 width=3334) (actual time=304.450..652504.217 rows=6 loops=1)
       -&gt;  Nested Loop  (cost=4355.35..61747.28 rows=1 width=3342) (actual time=304.419..652504.060 rows=6 loops=1)
             -&gt;  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)
                   -&gt;  Nested Loop  (cost=0.00..32.75 rows=1 width=16) (actual time=0.190..26.703 rows=758 loops=1)
                         -&gt;  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))
                         -&gt;  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 &gt; now()) AND ((startdate IS NULL) OR (startdate &lt; now()))) THEN 1 ELSE 0 END END = 1))
                   -&gt;  Nested Loop  (cost=4355.35..61692.86 rows=1069 width=16) (actual time=91.088..843.967 rows=79986 loops=758)
                         -&gt;  Nested Loop  (cost=4355.35..54185.33 rows=1069 width=8) (actual time=91.065..555.830 rows=79986 loops=758)
                               -&gt;  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)
                               -&gt;  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 &gt; now()) AND ((startdate IS NULL) OR (startdate &lt; now()))) THEN 1 ELSE 0 END END = 1))
                                     -&gt;  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)
                         -&gt;  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)
             -&gt;  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))
       -&gt;  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)
  </code></pre>
  
  <p>I am reading and reading forum posts and the user manual, but I can't figure out what would make this run faster, except maybe if it were possible to make indexes for the select that uses the <code>now()</code> function.</p>