Index a view of a join with Postgresql?

Question

I have a select statement that makes a very simple query from a more complicated view:

I have a fairly straight-forward select statement....

    SELECT
          uid
    FROM userpermissions
    WHERE
            uid         = :whoami
        AND
            application = :application
        AND
            subsystem   = :subsystem
    ;

And my view is only ints and varchars, but a join of four tables (likely to be the real problem).

                       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;

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.

My other thought was to use a memcache, but that feels like a band-aid solution to the problem of an inefficient view.

Here's a profile of the select:

                                                        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)
   ->  Nested Loop  (cost=1.18..3.34 rows=1 width=8) (actual time=0.040..0.040 rows=0 loops=1)
         ->  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)
               ->  Seq Scan on groupaccess  (cost=0.00..1.43 rows=43 width=8) (actual time=0.014..0.014 rows=1 loops=1)
               ->  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
                     ->  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))
         ->  Index Scan using groups_pkey on groups  (cost=0.00..0.55 rows=1 width=4) (never executed)
               Index Cond: (gid = groupaccess.gid)
   ->  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)

Which totally baffles me, because the moment I put it into PDO with PHP the query takes seconds, not fractions of a second.

Answer 1

A view does not help with performance. It is only good to simplify things, grant specific rights and some such. But it has no benefit for query performance.

You could try to cut out the middle-man (the view) and use this query:

SELECT u.uid
FROM   users u
JOIN   groupaccess g USING (gid)
JOIN   subsystems  s USING (sid)
WHERE  u.uid = :whoami
  AND  s.application = :application
  AND  s.subsystem   = :subsystem;

Which also cuts out another middle-man, the table groups, which is not needed at all in your scenario. (Except if the connecting row for a user could be missing, which should not be possible.)

For performance, you would have to make that a materialized view, which is a different beast altogether.