title: Index a view of a join with Postgresql?
body: <p>I have a select statement that makes a very simple query from a more complicated view:</p>
  
  <p>I have a fairly straight-forward select statement....</p>
  
  <pre><code>    SELECT
            uid
      FROM userpermissions
      WHERE
              uid         = :whoami
          AND
              application = :application
          AND
              subsystem   = :subsystem
      ;
  </code></pre>
  
  <p>And my view is only ints and varchars, but a join of four tables (likely to be the real problem).</p>
  
  <pre><code>                       View "public.userpermissions"
     Column    |          Type          | Modifiers | Storage  | Description
  -------------+------------------------+-----------+----------+-------------
   uid         | integer                |           | plain    |
   gid         | integer                |           | plain    |
   sid         | integer                |           | plain    |
   name        | character varying(128) |           | extended |
   application | character varying(128) |           | extended |
   subsystem   | character varying(128) |           | extended |
  View definition:
   SELECT users.uid, groups.gid, groupaccess.sid, groups.name, subsystems.application, subsystems.subsystem
     FROM users
     JOIN groups ON groups.gid = users.gid
     JOIN groupaccess ON groups.gid = groupaccess.gid
     JOIN subsystems ON subsystems.sid = groupaccess.sid;
  </code></pre>
  
  <p>I'm unsure how to effectively update the view so that my queries are more effective, as they're taking about 1-4 seconds right now, and in some cases up to 8.</p>
  
  <p>My other thought was to use a memcache, but that feels like a band-aid solution to the problem of an inefficient view.</p>
  
  <p>Here's a profile of the select:</p>
  
  <pre><code>                                                        QUERY PLAN
  ----------------------------------------------------------------------------------------------------------------------------
   Nested Loop  (cost=1.18..4.54 rows=1 width=4) (actual time=0.043..0.043 rows=0 loops=1)
     Join Filter: (groups.gid = users.gid)
     -&gt;  Nested Loop  (cost=1.18..3.34 rows=1 width=8) (actual time=0.040..0.040 rows=0 loops=1)
           -&gt;  Hash Join  (cost=1.18..2.78 rows=1 width=4) (actual time=0.039..0.039 rows=0 loops=1)
                 Hash Cond: (groupaccess.sid = subsystems.sid)
                 -&gt;  Seq Scan on groupaccess  (cost=0.00..1.43 rows=43 width=8) (actual time=0.014..0.014 rows=1 loops=1)
                 -&gt;  Hash  (cost=1.17..1.17 rows=1 width=4) (actual time=0.017..0.017 rows=0 loops=1)
                       Buckets: 1024  Batches: 1  Memory Usage: 0kB
                       -&gt;  Seq Scan on subsystems  (cost=0.00..1.17 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=1)
                             Filter: (((application)::text = 'LoginLink'::text) AND ((subsystem)::text = '1'::text))
           -&gt;  Index Scan using groups_pkey on groups  (cost=0.00..0.55 rows=1 width=4) (never executed)
                 Index Cond: (gid = groupaccess.gid)
     -&gt;  Seq Scan on users  (cost=0.00..1.19 rows=1 width=8) (never executed)
           Filter: (uid = 2)
   Total runtime: 0.192 ms
  (15 rows)
  </code></pre>
  
  <p>Which totally baffles me, because the moment I put it into PDO with PHP the query takes seconds, not fractions of a second.</p>