Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question
 
how long it takes when you using pg_query? And, how far is your database server? –  doctore Jan 16 '12 at 16:16
 
DB and Web are same machine. Command-line psql above indicates 0.192ms, when calling from PHP it indicates 3.6 seconds. –  Incognito Jan 16 '12 at 16:18
 
0.192 ms it's the time in the database server, but if you use the function pg_query of PHP instance of PDO functions, how long does it take? –  doctore Jan 16 '12 at 16:30
 
Using PDO, the query takes 0.09 seconds. I don't have pg_query in my code. Now I'm more confused... I don't think the query is my problem at all. –  Incognito Jan 16 '12 at 16:31
 
DB and Web are same machine, but are you calling the PHP script from another machine? The only thing I can think of is you may have a delay with the network. –  doctore Jan 16 '12 at 16:32
show 5 more comments

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.