索引与 Postgresql 的连接视图？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8882593/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Index a view of a join with Postgresql?
提问by Incognito
I have a select statement that makes a very simple query from a more complicated view:
我有一个 select 语句，它从一个更复杂的角度进行了一个非常简单的查询：
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).
我的观点只是 ints 和 varchars，而是四个表的连接（可能是真正的问题）。
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.
我不确定如何有效地更新视图以使我的查询更有效，因为它们现在需要大约 1-4 秒，在某些情况下最多需要 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.
这完全让我感到困惑，因为当我用 PHP 将它放入 PDO 时，查询需要几秒钟，而不是几分之一秒。
回答by Erwin Brandstetter
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.)
这也减少了另一个中间人 table groups，这在您的场景中根本不需要。（除非用户的连接行可能丢失，这应该是不可能的。）
For performance, you would have to make that a materialized view, which is a different beast altogether.
为了性能，你必须把它变成一个物化视图，这是一个完全不同的野兽。
